Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello again community.
Could you please help me with this simple human logic (I think), but a little tricky for DAX calculations? I have 90% of work done, but I cannot implement last logic to the measure because I am not undertanding how to arrange / fix table massage error (I think because I am using VALUES which brings / spected one single value).
I already have validated when user does not select anything (will be blank).
Problem: I have an external Slicer (created a non related table with distinct values), but I don't know how to SUM UP measure YTD PP when user selects more than one option. When they do, error message appears.
DAX Measure:
YTD Investment in Pure Player = IF ( HASONEVALUE ( PurePlayers_NOT_Related[PURE PLAYER] ), CALCULATE ( [Mthy Bil], FILTER ( 'Date', 'Date'[Month Number] < MONTH ( TODAY () ) ), FILTER ( MiddleOffice, MiddleOffice[GLOBAL GROUP] <> "EXTERNAL" && MiddleOffice[GLOBAL GROUP] <> "MIDDLE OFFICE" ), FILTER ( MiddleOffice, MiddleOffice[PURE PLAYER] = VALUES ( PurePlayers_NOT_Related[PURE PLAYER] ) ) ), BLANK () )
I think the problem is related when I try to filter. I don't know how to bring more than one value selection and then sum up
MiddleOffice[PURE PLAYER] = VALUES ( PurePlayers_NOT_Related[PURE PLAYER] )
Correct scenario (one slicer value selection):
Incorrect scenario (more than one slicer value selection): Correct visualization of YTD PP should be 3.866.462 (AFFIPERF + MOBEXT)
Thanks in advance community.
Solved! Go to Solution.
Hi all, Hi @Dog again.
I could work by myself this entire afternoon and now I finally got a solution 🙂
What I did to resolve the Slicer Issue? (final table result as follow below):
Updated measures:
*** YTD Investment in FO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** YTD Investment in MO = IF ( CALCULATE ( [Mthy Bil]; ALL ( PP_Slicer ); ALLEXCEPT ( MiddleOffice; MiddleOffice[YEAR FPM]; Client[CLIENT] ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Mthy Bil]; ALL ( PP_Slicer ); ALLEXCEPT ( 'Date'; 'Date'[Year] ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to Tableau / IOMT data *** YTD Investment in Pure Player = // ### USING FIRST (active) RELATIONSHIP ### IF ( NOT ( ISFILTERED ( PP_Slicer[PURE PLAYER] ) ), BLANK (), CALCULATE ( [Mthy Bil], FILTER ( 'Date', 'Date'[Month Number] < MONTH ( TODAY () ) ), FILTER ( MiddleOffice, MiddleOffice[GLOBAL GROUP] <> "EXTERNAL" && MiddleOffice[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) *** FC Investment in FO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** FC Investment in MO = IF ( CALCULATE ( [Invest Pilot BRL]; ALL(PP_Slicer); ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "PURE PLAYERS" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK(); CALCULATE ( [Invest Pilot BRL]; ALL(PP_Slicer); ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "PURE PLAYERS" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** FC Investment in Pure Player = // ### USING SECOND (inactive) RELATIONSHIP IF ( NOT ( ISFILTERED ( PP_Slicer[PURE PLAYER] ) ); BLANK (); CALCULATE ( [Invest Pilot BRL]; USERELATIONSHIP ( Pilot[PURE PLAYER]; PP_Slicer[PURE PLAYER] ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( Pilot; Pilot[GLOBAL GROUP] <> "EXTERNAL" && Pilot[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) )
I hope this scenario can help some other people, at least with insights.
Thanks all again and best regards,
Hi,
is it not possible to create a relationship between your
PurePlayers_NOT_Related
table and the
MiddleOffice
table and just allow that to filter down the figures? I'd imagine that you would then only need to remove the red text in your measure?
Dog
Hi @Dog, how are you? Thanks a lot for your reply...
Yeah, you are right. Easiest way , but in my case I will have another issue. Let me try to give full scenario. Maybe you can get the picture and help me with a kind of "manual summation" using a slicer that do not intefere in a another measure in the same visual (in this case a table or matrix).
I am sharing Files:
*** From Pilot table I have measures:
YTD FO (freezed) = Billings Jan17-Sep17 EXCEPT AFFIPERF, MOBEXT, ECSELIS and SOCIALYSE. Slicer CANNOT interfere!;
FC FO (freezed) = Forecast Oct17-Dec17 EXCEPT AFFIPERF, MOBEXT, ECSELIS and SOCIALYSE. Slicer CANNOT interfere!;
FC MO (freezed) = Forecast Oct17-Dec17 JUST FOR AFFIPERF, MOBEXT, ECSELIS and SOCIALYSE. Slicer CANNOT interfere!;
FC PP (flexible) = Forecast Oct17-Dec17 ACCORDING TO slicer. If all selected result will be = FC MO. Slicer HAS TO interfere!;
*** From MiddleOffice table I have measures:
YTD MO = Billings Jan17-Sep17 JUST FOR AFFIPERF, MOBEXT, ECSELIS and SOCIALYSE. Slicer CANNOT interfere!;
YTD PP = Billings Jan17-Sep17 ACCORDING TO slicer. If all selected result will be = YTD MO. Slicer selection HAS TO interfere!;
Measures:
*** YTD FO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** YTD MO = IF ( CALCULATE ( [Mthy Bil];ALL(PurePlayers_NOT_Related); ALLEXCEPT ( MiddleOffice; MiddleOffice[YEAR FPM]; Client[CLIENT] ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Mthy Bil];ALL(PurePlayers_NOT_Related); ALLEXCEPT ( 'Date';'Date'[Year] ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to Tableau / IOMT data (MiddleOffice Table) *** YTD PP = IF ( NOT ( ISFILTERED ( PurePlayers_NOT_Related[PURE PLAYER] ) ); BLANK (); CALCULATE ( [Mthy Bil]; FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( MiddleOffice; MiddleOffice[GLOBAL GROUP] <> "EXTERNAL" && MiddleOffice[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) *** FC FO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** FC MO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "PURE PLAYERS" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK(); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "PURE PLAYERS" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** FC PP = IF ( NOT ( ISFILTERED ( PurePlayers_NOT_Related[PURE PLAYER] ) ); BLANK (); CALCULATE ( [Mthy Bil]; FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( MiddleOffice; MiddleOffice[GLOBAL GROUP] <> "EXTERNAL" && MiddleOffice[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) )
Thanks again in advance,
Hi all, Hi @Dog again.
I could work by myself this entire afternoon and now I finally got a solution 🙂
What I did to resolve the Slicer Issue? (final table result as follow below):
Updated measures:
*** YTD Investment in FO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** YTD Investment in MO = IF ( CALCULATE ( [Mthy Bil]; ALL ( PP_Slicer ); ALLEXCEPT ( MiddleOffice; MiddleOffice[YEAR FPM]; Client[CLIENT] ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Mthy Bil]; ALL ( PP_Slicer ); ALLEXCEPT ( 'Date'; 'Date'[Year] ); FILTER ( 'Date'; 'Date'[Month Number] < MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to Tableau / IOMT data *** YTD Investment in Pure Player = // ### USING FIRST (active) RELATIONSHIP ### IF ( NOT ( ISFILTERED ( PP_Slicer[PURE PLAYER] ) ), BLANK (), CALCULATE ( [Mthy Bil], FILTER ( 'Date', 'Date'[Month Number] < MONTH ( TODAY () ) ), FILTER ( MiddleOffice, MiddleOffice[GLOBAL GROUP] <> "EXTERNAL" && MiddleOffice[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) *** FC Investment in FO = IF ( CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK (); CALCULATE ( [Invest Pilot BRL]; ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "OTHERS" ); FILTER ( Pilot; Pilot[MEDIA TYPE] = "MEDIA - ON" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** FC Investment in MO = IF ( CALCULATE ( [Invest Pilot BRL]; ALL(PP_Slicer); ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "PURE PLAYERS" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) = BLANK (); BLANK(); CALCULATE ( [Invest Pilot BRL]; ALL(PP_Slicer); ALLEXCEPT ( Pilot; Pilot[YEAR] ); FILTER ( Pilot; Pilot[VEHICLE THROUGH] = "PURE PLAYERS" ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( GlobalGroup; GlobalGroup[GLOBAL GROUP] <> "EXTERNAL" && GlobalGroup[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) ) // According to PILOT data *** FC Investment in Pure Player = // ### USING SECOND (inactive) RELATIONSHIP IF ( NOT ( ISFILTERED ( PP_Slicer[PURE PLAYER] ) ); BLANK (); CALCULATE ( [Invest Pilot BRL]; USERELATIONSHIP ( Pilot[PURE PLAYER]; PP_Slicer[PURE PLAYER] ); FILTER ( 'Date'; 'Date'[Month Number] >= MONTH ( TODAY () ) ); FILTER ( Pilot; Pilot[GLOBAL GROUP] <> "EXTERNAL" && Pilot[GLOBAL GROUP] <> "MIDDLE OFFICE" ) ) )
I hope this scenario can help some other people, at least with insights.
Thanks all again and best regards,
Final table:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |