Forum Discussion

David01's avatar
David01
Frequent Visitor
4 years ago
Solved

Dax not returning a value

I am having difficulty getting the following to work any help would be appreciated.

 

IF( MAX(Machine[Display])="Precision"
,CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"})
,CALCULATE( SUM(Manufacturing[TonnesMade])))

 

TRIED

 

VAR = CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"})

RETURN
IF( MAX(Machine[Display])="Precision"
,VAR
,CALCULATE( SUM(Manufacturing[TonnesMade])))


If I do the following

 

IF( MAX(Machine[Display])="Precision"
,12345.01
,CALCULATE( SUM(Manufacturing[TonnesMade])))

 

It returns the 12345.01 for Precision otherwise it wont even show Precision.


All other results are shown as expected for any of the variations I have tried.

 

The CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"}) does return a value if i use it in a card as a test

 

the table Manufacturing and Machine have a relationship one to many.

DAX
  • David01's avatar
    David01
    4 years ago

    I changed this 
    IF( MAX(Machine[Display])="Precision"
    ,CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"})
    ,CALCULATE( SUM(Manufacturing[TonnesMade])))

    To

    IF( MAX(Machine[Display])="Precision"
    ,CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"},

    allexcept(Machine,Machine[Alt Name])

    )
    ,CALCULATE( SUM(Manufacturing[TonnesMade])))

    It now works as I required.

    I will be the first to acknowledge I have no idea what the allexcept is acually doing.

    Thanks for looking at this for me.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    David01 , can share some visual screen shot. the above should work

     

    Try this too

    CALCULATE(SUM(Manufacturing[TonnesMade]),Filter(Machine,  Machine[Alt Name] IN {"MA","MB","MC","MD"}) )

     


    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • David01's avatar
      David01
      Frequent Visitor
      4 years ago

      I changed this 
      IF( MAX(Machine[Display])="Precision"
      ,CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"})
      ,CALCULATE( SUM(Manufacturing[TonnesMade])))

      To

      IF( MAX(Machine[Display])="Precision"
      ,CALCULATE(SUM(Manufacturing[TonnesMade]),Machine[Alt Name] IN {"MA","MB","MC","MD"},

      allexcept(Machine,Machine[Alt Name])

      )
      ,CALCULATE( SUM(Manufacturing[TonnesMade])))

      It now works as I required.

      I will be the first to acknowledge I have no idea what the allexcept is acually doing.

      Thanks for looking at this for me.