How to show empty columns

[KainLittleCloth]

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!!

[VahidDM]

Hi @KainLittleCloth 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[KainLittleCloth]

Hi @VahidDM ,
can i send u a link to my older post? It may have more content. Thanks for your help!
Solved: Re: Creating tables from a dataset - Microsoft Fabric Community

[VahidDM]

Hi @KainLittleCloth 

Can you post sample data as text here? or share PBIX file after removing sensetive data?

[KainLittleCloth]

 @VahidDM , sure!

Below is my dataset sample:

This is my desired output:

This is what i achieved so far:

1. I want to show the "NA" column even though it is empty.

2. I want a summary for each quarter.

Many thanks!

[VahidDM]

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

[KainLittleCloth]

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

[v-rzhou-msft · Accepted solution]

Hi @KainLittleCloth ,

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.