Forum Discussion

MikeHendriks's avatar
MikeHendriks
Helper 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 special wish. This is my output:

This looks ok, but I have 1 special thing; the columns Number and Subtype are coming from Costcenter. And when I have Subtypes NXO ánd VXO (like on number 12) I don't want the total to sum up these, but just use it only once. So total in this example must be 290, because number 12 = 90, number 100 = 100 and 23 = 100.

My current Capacity 2 measure is:

Capacity 2 measure =

VAR CurDate = MAX('Calendar'[Date])
VAR CurDateMin = MIN('Calendar'[Date])

VAR x = CALCULATE(
    SUM('Fact'[Capacity 2]),
    NOT ISBLANK( 'Fact'[Capacity 2])
    && OR(CurDate <= 'Fact'[Enddate], CurDateMin <= 'Fact'[Enddate])
    && OR(CurDate >= 'Fact'[Startdate], CurDateMin >= 'Fact'[Startdate])
)

RETURN x


I've tried many things like SUMMARIZING costcenter on Number and subtype and so on, but every time I get stuck.

Is there a DAX crack here that can help me with the solution?

PBIX example can be found here: https://file.io/nfbQbg5K6Dgx

Need Help

19 Replies

  • MikeHendriks's avatar
    MikeHendriks
    Helper I
    2 years ago

    Hi Ahmedx,

     

    Thank you for your quick response! This seems to work for the data that is in the example, but I saw on your code that it's not working for cases where only VXO is present (and NXO is not). I've added that case to https://easyupload.io/1j4mpj with costcenter 567.

     

    Is there an option which is working not with an approach where VXO is not calculated, but just 1 value for VXO and NXO when both are present. They will by the way always be equal.

    • Ahmedx's avatar
      Ahmedx
      Super User
      2 years ago

      I can’t help you, you better get a new version on your personal computer and see my solution

      • MikeHendriks's avatar
        MikeHendriks
        Helper 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.