Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm sure there is a rather simple answer to this need, but I don't know what it is. (I fear I'm misisng the obvious.) I'm not very familiar with DAX yet either. If anyone can guide me a bit that'd be great.
I have a table in a Power BI dataset with a couple thousand rows. Some of the columns are like the following example.
ID Language Type 12345 eng 3 67890 fra 1 54321 spa 1 09876 cmn 2 abc12 eng 2 34def cmn 1 56fed por 2
I require a filtered result containing rows where Languages are a Type 3 or 2, but where the same Language is NEVER found in the table with Type 1. In this example I would want [eng, por]. I would not want cmn.
Thanks for any help.
Jeff
Solved! Go to Solution.
HI @jklassen
Please try this calculated Table
From the Modelling Tab>>>NEW TABLE
Filtered_Table = VAR temp1 = CALCULATETABLE ( VALUES ( Table1[Language] ), Table1[Type] IN { 2, 3 } ) VAR temp2 = CALCULATETABLE ( VALUES ( Table1[Language] ), Table1[Type] = 1 ) RETURN EXCEPT ( temp1, temp2 )
HI @jklassen
Please try this calculated Table
From the Modelling Tab>>>NEW TABLE
Filtered_Table = VAR temp1 = CALCULATETABLE ( VALUES ( Table1[Language] ), Table1[Type] IN { 2, 3 } ) VAR temp2 = CALCULATETABLE ( VALUES ( Table1[Language] ), Table1[Type] = 1 ) RETURN EXCEPT ( temp1, temp2 )
Zubair,
Thank you, for an accurate and quick reply. I have adapated your solution for my data and it's working as needed.
Please see attached file
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |