Forum Discussion
HELP! Pivot Sorting
You may add column (let say "not only USA") as
= VAR No = [#] VAR c = [Country] VAR withoutUSA = CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1 ), Table1[#] = No, Table1[Country] <> "USA" ) VAR withUSA = CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1 ), Table1[#] = No, Table1[Country] = "USA" ) RETURN withoutUSA > 0 && withUSA > 0and make filter on it
Thank you so much for this because I am learning a lot and quite amazed at how much excel can do.
The next issue (Im so sorry... thank you for your time) is
-91
-100040
-USA
-USA
-100200
-USA
-USA
-UK
-123402
-USA
-142389
-USA
-France
-156789
-France
etc
how can I tell excel to differentiate trip # that are USA and another country. If it is just USA or multiple USA I do not want to see it (same if it is just one foreign country or multiple foreign countries). Only trip # that have USA (either once or multiple) AND another country (once or multiple)... I have a feeling it may be an if function but am unsure how to tell excel
***in the image provided, I only want to see trip # such as 101544, 101755, 101783.. trip # such as 101433 with multiple USA or other trip # with multiple Foreign countries and no USA I do not want to see... they have to have both
Thank you again for your help
You may add column (let say "not only USA") as
=
VAR No = [#]
VAR c = [Country]
VAR withoutUSA =
CALCULATE (
COUNTROWS ( Table1 ),
ALL ( Table1 ),
Table1[#] = No,
Table1[Country] <> "USA"
)
VAR withUSA =
CALCULATE (
COUNTROWS ( Table1 ),
ALL ( Table1 ),
Table1[#] = No,
Table1[Country] = "USA"
)
RETURN
withoutUSA > 0
&& withUSA > 0and make filter on it
- Wow, Incredible. THANK YOU!