Forum Discussion

balu810's avatar
balu810
Advocate I
4 years ago
Solved

show all values in chart

i have a month filter which is filtering the chart with the respective selections , i have an requirments where i need to show all employees irrespective of selection in month and show 0 for them ,,i tried show values with no data but its not working .

 

Note : chart has to filter the employees according to slicer selection , only month selection should return all employees .

 

EMP IDMonthYearManager IDPlanned TimeRecorded Time
1Apr202223528
2Mar202253529
3Jan202223530
4Feb202253525
5Mar202253535

 

 values in chart has below caluclated column

 

Missing Time = IF('Table'[Recorded Time]>'Table'[Planned Time], 0 ,'Table'[Planned Time]-'Table'[Recorded Time])
Need Help
Show and Tell
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi  balu810 , 

     

    You should create a new table only contains Year and Month, and use these columns to do filter.

    Date slicer = crossjoin(VALUES('Table'[Year]),VALUES('Table'[Month]))
    Use this expression to calcualte the Missing Time:

    Measure =
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Missing Time] ),
        FILTER (
            'Table',
            [Year]
                IN VALUES ( 'Date slicer'[Year] )
                    && [Month] IN VALUES ( 'Date slicer'[Month] )
        )
    )
RETURN
    IF ( ISBLANK ( _c ), 0, _c )

    Result:

     

    Pbix in the end you can refer.
    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi  balu810 , 

     

    You should create a new table only contains Year and Month, and use these columns to do filter.

    Date slicer = crossjoin(VALUES('Table'[Year]),VALUES('Table'[Month]))
    Use this expression to calcualte the Missing Time:

    Measure =
VAR _c =
    CALCULATE (
        SUM ( 'Table'[Missing Time] ),
        FILTER (
            'Table',
            [Year]
                IN VALUES ( 'Date slicer'[Year] )
                    && [Month] IN VALUES ( 'Date slicer'[Month] )
        )
    )
RETURN
    IF ( ISBLANK ( _c ), 0, _c )

    Result:

     

    Pbix in the end you can refer.
    Best Regards

    Community Support Team _ chenwu zhu

     

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