Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to show empty columns

Hi all! How do i show the columns even there is not values in it? 

This is how mine looks like:

 

The "NA" does not show because all of them has values either for L1 or L2, but i would like to see the column to be there even though there is no value.


My desired output:

The measure for "NA"

= if [Value]="L1" or [Value]="L2" then [Value] else "NA"

Just to add on, I tried selelecting "Show items with no data" in the values field for all but nothing changes.

 

 

Question 2: How do i show a summary for each quarter like this?


Really apreciate your time and effort!!

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    Here I unpivot the table as well.

    I suggest you to create an unrelated dim table to help calculation.

    DimValue = 
VAR _GENERATE1 = GENERATE(
    DATATABLE(
    "Month",STRING,
    "Order for Month",INTEGER,
    {
        {"Jan",1},
        {"Feb",2},
        {"Mar",3},
        {"Previous Summary",4}
        }
    ),VALUES('Table'[Session]))
VAR _GENERATE2 = 
GENERATE(
    _GENERATE1,
DATATABLE(
    "Value",STRING,
    "Order for Value",INTEGER,
    {
        {"L1",1},
        {"L2",2},
        {"NA",3}
        }
    ))
RETURN
_GENERATE2

    Measure:

    MonthCount(L1) = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Tenor] = MAX ( 'Table'[Tenor] )
            && 'Table'[Month] = MAX ( DimValue[Month] )
            && 'Table'[Session] = MAX ( DimValue[Session] )
            && 'Table'[Value] = "L1"
    )
)
    MonthCount(L2) = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Tenor] = MAX ( 'Table'[Tenor] )
            && 'Table'[Month] = MAX ( DimValue[Month] )
            && 'Table'[Session] = MAX ( DimValue[Session] )
            && 'Table'[Value] = "L2"
    )
)
    MEASURE = 
VAR _SUML1 =
    CALCULATE (
        SUMX (
            FILTER (
                ALLSELECTED ( DimValue ),
                DimValue[Session] = MAX ( DimValue[Session] )
                    && DimValue[Value] = "L1"
            ),
            [MonthCount(L1)]
        )
    )
VAR _SUML2 =
    CALCULATE (
        SUMX (
            FILTER (
                ALLSELECTED ( DimValue ),
                DimValue[Session] = MAX ( DimValue[Session] )
                    && DimValue[Value] = "L2"
            ),
            [MonthCount(L2)]
        )
    )
RETURN
    IF (
        MAX ( DimValue[Month] ) IN VALUES ( 'Table'[Month] ),
        SWITCH (
            MAX ( DimValue[Value] ),
            "L1", [MonthCount(L1)],
            "L2", [MonthCount(L2)],
            "NA", IF ( [MonthCount(L1)] = [MonthCount(L2)], [MonthCount(L1)], BLANK () )
        ),
        SWITCH (
            MAX ( DimValue[Value] ),
            "L1", _SUML1,
            "L2", _SUML2,
            "NA", IF ( _SUML1 = _SUML2, _SUML1, BLANK () )
        )
    )

    Result is as below.

     

     

    Best Regards,
    Rico Zhou

     

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

7 Replies