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 lik...
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.

VahidDM's avatar
VahidDM
Super User
1 year ago

How big is your data? 1b rows, 1m rows, 1000 rows?
Do you want it blank or "-" or Space would be ok?

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

My data is around 400 rows only but it is still adding as days goes by. I want it blank. Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.