Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
KainLittleCloth
Frequent Visitor

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:

KainLittleCloth_1-1730856678890.png

 

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:

KainLittleCloth_0-1730856658723.png

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?

KainLittleCloth_3-1730856678892.png


Really apreciate your time and effort!!

1 ACCEPTED SOLUTION

Hi @KainLittleCloth ,

 

Here I unpivot the table as well.

vrzhoumsft_0-1730964631744.png

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.

vrzhoumsft_1-1730964698368.png

 

 

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.

View solution in original post

7 REPLIES 7
VahidDM
Super User
Super User

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/

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

Hi @KainLittleCloth 

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

 

 @VahidDM , sure!

Below is my dataset sample:

KainLittleCloth_0-1730861762449.png

 

This is my desired output:

KainLittleCloth_1-1730861807149.png



This is what i achieved so far:

KainLittleCloth_2-1730861861819.png

 

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

2. I want a summary for each quarter.

 

Many thanks!

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

 

 

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

Hi @KainLittleCloth ,

 

Here I unpivot the table as well.

vrzhoumsft_0-1730964631744.png

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.

vrzhoumsft_1-1730964698368.png

 

 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.