Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi community, good afternoon!!!
I have the following chalange that I cant resolve about bands assistance:
I need to obtain a result out of a column called (Assistance YES OR NO which have blanks too) of my excel table for example:
If we consider 3 months and 3 bands if they assisted to rehearse 3 consecutive months they get a discount. meaning that if they have a "NO" any month of the three they won't get the discount. (When the column is "BLANK") we dont have to consider that as a "NO" I just don't need to consider blanks in my calculation.
CAN YOU GUYS SUGGEST ME A FORMULA DAX TO SOLVE THAT?
any advice would be so much appreciated!!!: (down below is how my table looks like)
ASSISTANCE TABLE | RESUME 3 LAST MONTHS | ||||
MUSIC BANDS | MONTHS | ASSISTANCE YES/NO | MUSIC BANDS | DISCOUNT (EXPECTED RESULT) | |
BAND1 | October | YES | BAND1 | YES | |
BAND2 | October | BAND2 | YES | ||
BAND3 | October | NO | BAND3 | NO | |
BAND1 | November | YES | |||
BAND2 | November | YES | |||
BAND3 | November | YES | |||
BAND1 | December | YES | |||
BAND2 | December | YES | |||
BAND3 | December | YES |
Solved! Go to Solution.
To achieve your requirement, you may create a measure using DAX formlula below then drag MUSIC BANDS and measure to a table visual.
DISCOUNT (EXPECTED RESULT) = IF ( CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[MUSIC BANDS] ), Table1[MONTHS] IN { "December", "November", "October" } && ( Table1[ASSISTANCE YES/NO] = "YES" || Table1[ASSISTANCE YES/NO] = BLANK () ) ) ) = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[MUSIC BANDS] ), Table1[MONTHS] IN { "December", "November", "October" } ) ), "YES", "NO" )
Regards,
Jimmy Tao
To achieve your requirement, you may create a measure using DAX formlula below then drag MUSIC BANDS and measure to a table visual.
DISCOUNT (EXPECTED RESULT) = IF ( CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[MUSIC BANDS] ), Table1[MONTHS] IN { "December", "November", "October" } && ( Table1[ASSISTANCE YES/NO] = "YES" || Table1[ASSISTANCE YES/NO] = BLANK () ) ) ) = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[MUSIC BANDS] ), Table1[MONTHS] IN { "December", "November", "October" } ) ), "YES", "NO" )
Regards,
Jimmy Tao
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi, @LivioLanzo!...thats correct, the blanks would be considered always as a YES.
The data contain all data, meaning "more months" and I need to always consider the last 3 months.
Real data looks like this pic (little example)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |