Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I need some help creating two dax measures that I will use as new columns in a Power BI Table Visual.
Below are the specifications as well as data samples and color coded resut prototypes.
Sample Table With Three Columns
Ticket | Class_ID | Pass_Fail |
1 | 123 | Passed |
2 | 333 | Passed |
3 | 232 | Passed |
4 | 4 | Passed |
5 | 333 | Passed |
6 | 123 | Passed |
7 | 333 | Failed |
8 | 232 | Passed |
9 | 232 | Passed |
10 | 4 | Failed |
Dax1.
What I need is to categorize all the Passed Tickets that either have a unique (orphaned) Class_ID and those that have more than one Class ID. Below is an example with the new clumns added called "Category (DAX1)".
I need a Measure (but not a calculated column, because I work off direct query), that would exclude Failed Tickets and would categorise the passed ones into those with Multiple (shared) Class_ID and those with Unique Class ID:
Below is a screenshot of the resulting prototype for Dax1: (New Column: Category (Dax1).
Dax2.
Another Matrix I need is a way of showing how many tickets are there per each class ID.
I am assuming some sort of Dax is needed that uses Concatenatex function to build that measure.
Below is the prototype of the resulting table visual I need (again, failed Tickets must be excluded)
Dax2 is the neasure I need for "Tickets List (Dax2)" (New Column in Table Visual that would be a result of Dax2 measure).
Thank You so much in advance!
Solved! Go to Solution.
Thank you so much! (OMG Greg Deckler replied to me - I am so star struck right now! I watch your videos on youtube and was about to purchase your CookBook! You are awesome!)
Your solution worked well for Dax1 (I will use this technique from now on!) and your solution for Dax2 had to be wrapped in "calculate" to work.
The updated solution for Dax2 is:
Again, thank you so much for a super quick response, I appreciate it very much.
@E12345 OK, second one should be:
Tickets List (Dax 2) = CONCATENATEX( 'Table', [Ticket], ", " )
First one maybe:
Category (DAX1) =
VAR __ClassID = MAX('Table'[Class_ID])
VAR __Count = COUNTROWS( FILTER( ALL('Table'), [Class_ID] = __ClassID && [Pass_Fail] = "Passed" )
VAR __Result = IF( __Count > 1, "Passed Ticket With Shared Class_ID", "Passed Ticket With Unique Class_ID" )
RETURN
__Result
Is there another way to write the "DAX1" equivalent? I already posted that the solution is found, but when I used the same approach with my real data, the measure "hangs" when I add it to a table so I cannot use it (the table just freezes)... It totally works with dummy data though! So, I was wondering if some sort of window function can be implemented to get the same results (perhaps it will for for my real data as well). TY!
Thank you so much! (OMG Greg Deckler replied to me - I am so star struck right now! I watch your videos on youtube and was about to purchase your CookBook! You are awesome!)
Your solution worked well for Dax1 (I will use this technique from now on!) and your solution for Dax2 had to be wrapped in "calculate" to work.
The updated solution for Dax2 is:
Again, thank you so much for a super quick response, I appreciate it very much.