Forum Discussion

animebuff's avatar
animebuff
Helper I
1 year ago
Solved

DAX issue

requirement:   left side visual is what by default we get in PBI what I need is right side visual but the values of all region should not be 0.31 rather apac - 1 europe - 0.5 la - 0.5 af...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi wini_R 

     

    Please try this measure:

    MEASURE =
VAR _currentRegion =
    SELECTEDVALUE ( 's rate'[region] )
VAR _ifdenominator =
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 's rate' ),
                's rate'[region] = _currentRegion
                    && 's rate'[inclusion flag] = "n"
            ),
            's rate'[mk],
            's rate'[region]
        )
    )
VAR _outcome =
    CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
        / COUNT ( 'S rate'[success] )
RETURN
    IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

animebuff's avatar
animebuff
Helper I
1 year ago

both methods will show data as 0 for months which doesn't have date surrogate key in fact table

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi wini_R 

 

Please try this measure:

MEASURE =
VAR _currentRegion =
    SELECTEDVALUE ( 's rate'[region] )
VAR _ifdenominator =
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 's rate' ),
                's rate'[region] = _currentRegion
                    && 's rate'[inclusion flag] = "n"
            ),
            's rate'[mk],
            's rate'[region]
        )
    )
VAR _outcome =
    CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
        / COUNT ( 'S rate'[success] )
RETURN
    IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )

The result is as follow:

 

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • animebuff's avatar
    animebuff
    Helper I
    1 year ago

    thanks Anonymous 

    your solution was perfect

    but now I face 2 problem in my offical PBI file,

    1. my interaction is not happening between year ( also month ) slicer with this metric

    we have 4 years ( 2021,2022, 2023 and 2024) 
    whatever year I'm selecting I'm getting all the years

    relationship between fact & dimension table is fine

     

    2. for ex. even if fact table not having date surrogate key for month year sep 21 

    I still data for sep 21 in the metric,

    sep 21 shows 1 or 100% and all region inside sep 21 also shows 1 or 100%