Forum Discussion

Pwc_PaK's avatar
Pwc_PaK
Regular Visitor
2 years ago

All Function for certain condition

Dax to get data from the version 1 when there is not other version row in the table based on the selected profit center.

MTD Actual Balance 2 =
VAR max_period_num = CALCULATE(MAX('MF4-MFG-PBQ'[PERIOD_NUM]))
VAR max_fiscal_year = MAX('MF4-MFG-PBQ'[FISCAL_YEAR])

VAR result =
    CALCULATE(
        SUM('MF4-MFG-PBQ'[Balance]),
        'MF4-MFG-PBQ'[PERIOD_NUM] = max_period_num,
        'MF4-MFG-PBQ'[FISCAL_YEAR] = max_fiscal_year,
        REMOVEFILTERS('MF4-MFG-PBQ'[Version.Version Level 01.Key]),
    if(
      CALCULATE(COUNTROWS('MF4-MFG-PBQ'),
     'MF4-MFG-PBQ'[Version.Version Level 01.Key]<>"1")=0,
     ALL('MF4-MFG-PBQ'[Profit Center.Level 03]),
     ALLSELECTED('MF4-MFG-PBQ'[Profit Center.Level 03])
       ),
        'MF4-MFG-PBQ'[Version.Version Level 01.Key]="1"
    )
RETURN result

dax is in error need help
DAX

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello Pwc_PaK , it will be really difficult to help you without more precise context. To test your measure, we need the dataset or at least, the error you get.

    • Pwc_PaK's avatar
      Pwc_PaK
      Regular Visitor
      2 years ago

      Hi Anonymous  thank you for replying , i have attached the screenshot of report.
      You can see in the table V1 is version and one of profit center 03 is not having versions other than 1 
      so in this case if one profit center don't have version and user select the version from slicer data should not be filters to that version only. it should give the rows which don't have version including the selected version data.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, Pwc_PaK 

        As Anonymous  mentions, it is not possible to simulate your data based on the information you have provided, you need to provide pbix files without sensitive data or information that can simulate your datasheets, Measure, and the effect you are expecting.

        Best Regards,
        Yang
        Community Support Team