Forum Discussion

MikeHendriks's avatar
MikeHendriks
Icon for Helper I rankHelper I
2 years ago
Solved

Measure based on dividing on types

Hi all,   I have a PBIX file with a fact table with numbers in it. These are linked to dimensions like costcenter.   But now I have an issue with my measure on total level because I have a specia...
Need Help
MikeHendriks's avatar
MikeHendriks
Icon for Helper I rankHelper I
2 years ago

I have downloaded your file Ahmedx. It works I think, but 

SWITCH(TRUE(),
MAX('Costcenter'[Type])="XXO"&&MAX('Costcenter'[SubType])="VXO",0,[Capacity 2 measure])
 
Gives issues in a case where VXO is present, but NXO is not.
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

in such a case what should we do?

  • MikeHendriks's avatar
    MikeHendriks
    Icon for Helper I rankHelper I
    2 years ago

    If VXO = 80 and NXO is not present -> 80
    If VXO is not present and NXO = 80 -> 80
    If VXO = 80 and NXO = 80 -> 80

      • MikeHendriks's avatar
        MikeHendriks
        Icon for Helper I rankHelper I
        2 years ago

        Unfortunately Ahmedx, this is not working. With my new added 'VXO only' example you get this:

        While I was expecting that 70 to be added here, so 360 in total.

        In my perspective this measure is wrong while it has 80 hardcoded:

        Capacity 3 measure = 
 VAR _t1 = MAX('Costcenter'[Number])
VAR _t2 =FILTER(ALL(Costcenter),'Costcenter'[Number]=_t1&&'Costcenter'[SubType]="VXO")
VAR _t3 = FILTER(ALL(Costcenter),'Costcenter'[Number]=_t1&&'Costcenter'[SubType]="NXO")
VAR _t4 =CALCULATE([Capacity 2 measure],_t2)
VAR _t5 =CALCULATE([Capacity 2 measure],_t3) 
 VAR _result = 
  SWITCH(TRUE(),
   ISBLANK(COUNTROWS(_t2)) && _t5=80,80,
   ISBLANK(COUNTROWS(_t3)) && _t4=80,80,
   _t4=80&&_t5=80,80,
        MAX('Costcenter'[Type])="XXO"&&MAX('Costcenter'[SubType])="VXO",0,[Capacity 2 measure])
RETURN _result

         

        File download with all examples in it: click