Solved: Show items with no data is not giving expected res...

AmiraBedh · ‎05-23-2024

I am using a Live connection to an SSAS tabular model having the following tables :

Employees : all Employees in the company

Employees VIP : Employees having the status of VIP in the company

Employees TIC : Employees having the status of TIC in the company

The relationships between the tables :

Employees - Employees VIP : 1 to many , filter in both directions based on EmpID

Employees - Employees TIC : 1 to 1 , filter in both directions based on EmpID

I am using in a visual columns from the 3 tables :

Employees : EmpID, EmpName, EmpLastName

Employees VIP : Status VIP

Employees TIC : Status TIC

I have this visual where I use data from Employees and Employees TIC :

When I try to add for the previous visual, columns from Employees VIP :

Now I want to show all the Employees, either they belong or not to any VIP or TIC so I selected Show items with no data :

When I try to change the order of the Staus VIP to be before Status TIC :

The display of data changes for the Employees E,F, G and H, I can"t understand this behaviour :

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

AmiraBedh · ‎05-24-2024

I ended up finding the answer to this behavior :

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

It's important to note that the mechanism of selecting values for the columns is order-dependent, and can be thought of as a Left outer join operation between tables. If the order of the columns is changed, the results change as well.

powerbi - Show items with no data is not giving expected result - Stack Overflow

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

AmiraBedh · ‎05-24-2024

I ended up finding the answer to this behavior :

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

It's important to note that the mechanism of selecting values for the columns is order-dependent, and can be thought of as a Left outer join operation between tables. If the order of the columns is changed, the results change as well.

powerbi - Show items with no data is not giving expected result - Stack Overflow

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

parry2k · ‎05-23-2024

@AmiraBedh regarding VIP table, I see it has one to many relationship, do you expect more than one record for employees, although TIC is 1 to 1

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AmiraBedh · ‎05-24-2024

If I change the relationship to 1 to 1 it gives the expected result, but I cannot change it in the real model as it is 1 to many. Any workaround ?

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

parry2k · ‎05-23-2024

@AmiraBedh so I reviewed the queries and the engine is doing different queries when you move the column positions in the visual:

Basically this part of the query is different in both the case and causing the result:

//showing tic then vip
// DAX Query
DEFINE
    VAR __DS0PrimaryShowAllCompat =
        //
        //					CALCULATETABLE(
        //						GENERATEALL(
        //							KEEPFILTERS(VALUES('Employees'[EmpName])),
        //							CALCULATETABLE(
        //								GENERATEALL(
        //									KEEPFILTERS(VALUES('Employees'[EmpLastName])),
        CALCULATETABLE (
            GENERATEALL (
                KEEPFILTERS (
                    FILTER (
                        KEEPFILTERS ( VALUES ( 'Employees TIC'[Status TIC] ) ),
                        OR (
                            CALCULATE ( NOT ( ISEMPTY ( 'Employees TIC' ) ) ),
                            CALCULATE ( NOT ( ISEMPTY ( 'Employees VIP' ) ) )
                        )
                    )
                ),
                CALCULATETABLE (
                    FILTER (
                        KEEPFILTERS ( VALUES ( 'Employees VIP'[Status VIP] ) ),
                        CALCULATE ( NOT ( ISEMPTY ( 'Employees VIP' ) ) )
                    )
                )
            )
        ) //								)
//							)

EVALUATE
__DS0PrimaryShowAllCompat

//vip first then tic

// DAX Query
DEFINE
    VAR __DS0PrimaryShowAllCompat =
        //        CALCULATETABLE (
        //            GENERATEALL (
        //                KEEPFILTERS ( VALUES ( 'Employees'[EmpLastName] ) ),
        CALCULATETABLE (
            GENERATEALL (
                KEEPFILTERS (
                    FILTER (
                        KEEPFILTERS ( VALUES ( 'Employees VIP'[Status VIP] ) ),
                        CALCULATE ( NOT ( ISEMPTY ( 'Employees VIP' ) ) )
                    )
                ),
                CALCULATETABLE (
                    FILTER (
                        KEEPFILTERS ( VALUES ( 'Employees TIC'[Status TIC] ) ),
                        CALCULATE ( NOT ( ISEMPTY ( 'Employees VIP' ) ) )
                    )
                )
            )
        ) //            )
//        )

EVALUATE
__DS0PrimaryShowAllCompat

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AmiraBedh · ‎05-23-2024

So it is a normal behavior?

How to achieve what I want in this case ?

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

parry2k · ‎05-23-2024

@AmiraBedh Very hard to reach, I wish you had put that as the code. Can you share the sample pbix file to look into it?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AmiraBedh · ‎05-23-2024

You can find the link to Google Drive : https://drive.google.com/drive/folders/1-rk-VKwYrGdbY7q_cjk_3TlBbAtH3U-H?usp=sharing

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

parry2k · ‎05-23-2024

@AmiraBedh that's interesting. Can you get the DAX query for both cases using a performance analyzer to see what is happening behind the scenes.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AmiraBedh · ‎05-23-2024

@parry2k

The query for the 1st visual :

DEFINE

VAR __DS0Core =

SELECTCOLUMNS(

KEEPFILTERS(

FILTER(

KEEPFILTERS(

SUMMARIZECOLUMNS(

'Employees'[EmpName],

'Employees'[EmpLastName],

'Employees TIC'[Status TIC],

'Employees VIP'[Status VIP],

"CountRowsEmployees_VIP", COUNTROWS('Employees VIP')

)

),

OR(

NOT(ISBLANK('Employees'[EmpName])),

NOT(ISBLANK('Employees'[EmpLastName]))

),

NOT(ISBLANK('Employees TIC'[Status TIC]))

),

NOT(ISBLANK('Employees VIP'[Status VIP]))

)

),

"'Employees'[EmpName]", 'Employees'[EmpName],

"'Employees'[EmpLastName]", 'Employees'[EmpLastName],

"'Employees TIC'[Status TIC]", 'Employees TIC'[Status TIC],

"'Employees VIP'[Status VIP]", 'Employees VIP'[Status VIP]

)

VAR __DS0PrimaryWindowed =

TOPN(

501,

__DS0Core,

'Employees'[EmpName],

1,

'Employees'[EmpLastName],

1,

'Employees TIC'[Status TIC],

1,

'Employees VIP'[Status VIP],

1

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

'Employees'[EmpName],

'Employees'[EmpLastName],

'Employees TIC'[Status TIC],

'Employees VIP'[Status VIP]

Then selecting the option Show items with no data :

// DAX Query DEFINE VAR __DS0Core = SELECTCOLUMNS( KEEPFILTERS( FILTER( KEEPFILTERS( SUMMARIZECOLUMNS( 'Employees'[EmpName], 'Employees'[EmpLastName], 'Employees TIC'[Status TIC], 'Employees VIP'[Status VIP], "CountRowsEmployees_VIP", COUNTROWS('Employees VIP') ) ),

OR( OR( OR( NOT(ISBLANK('Employees'[EmpName])), NOT(ISBLANK('Employees'[EmpLastName])) ),

NOT(ISBLANK('Employees TIC'[Status TIC])) ), NOT(ISBLANK('Employees VIP'[Status VIP])) ) ) ), "'Employees'[EmpName]", 'Employees'[EmpName], "'Employees'[EmpLastName]", 'Employees'[EmpLastName], "'Employees TIC'[Status TIC]", 'Employees TIC'[Status TIC], "'Employees VIP'[Status VIP]", 'Employees VIP'[Status VIP] ) VAR __DS0PrimaryShowAllCompat = FILTER( KEEPFILTERS( GENERATEALL( KEEPFILTERS( FILTER( KEEPFILTERS(VALUES('Employees'[EmpName])), OR( CALCULATE(NOT(ISEMPTY('Employees'))), CALCULATE(NOT(ISEMPTY('Employees VIP'))) ) ) ), CALCULATETABLE( GENERATEALL( KEEPFILTERS(VALUES('Employees'[EmpLastName])), CALCULATETABLE( GENERATEALL( KEEPFILTERS( FILTER( KEEPFILTERS(VALUES('Employees TIC'[Status TIC])), OR( CALCULATE(NOT(ISEMPTY('Employees TIC'))), CALCULATE(NOT(ISEMPTY('Employees VIP'))) ) ) ), CALCULATETABLE( FILTER( KEEPFILTERS(VALUES('Employees VIP'[Status VIP])), CALCULATE(NOT(ISEMPTY('Employees VIP'))) ) ) ) ) ) ) ) ), OR( OR( OR( NOT(ISBLANK('Employees'[EmpName])), NOT(ISBLANK('Employees'[EmpLastName])) ), NOT(ISBLANK('Employees TIC'[Status TIC])) ), NOT(ISBLANK('Employees VIP'[Status VIP])) ) ) VAR __DS0PrimaryShowAll = SUMMARIZE( UNION(__DS0Core, __DS0PrimaryShowAllCompat), 'Employees'[EmpName], 'Employees'[EmpLastName], 'Employees TIC'[Status TIC], 'Employees VIP'[Status VIP] ) VAR __DS0PrimaryWindowed = TOPN( 501, __DS0PrimaryShowAll, 'Employees'[EmpName], 1, 'Employees'[EmpLastName], 1, 'Employees TIC'[Status TIC], 1, 'Employees VIP'[Status VIP], 1 ) EVALUATE __DS0PrimaryWindowed ORDER BY 'Employees'[EmpName], 'Employees'[EmpLastName], 'Employees TIC'[Status TIC], 'Employees VIP'[Status VIP]

Then changing the order :

// DAX Query

DEFINE

VAR __DS0Core =

SELECTCOLUMNS(

KEEPFILTERS(

FILTER(

KEEPFILTERS(

SUMMARIZECOLUMNS(

'Employees'[EmpName],

'Employees'[EmpLastName],

'Employees VIP'[Status VIP],

'Employees TIC'[Status TIC],

"CountRowsEmployees_VIP", COUNTROWS('Employees VIP')

)

),

OR(

NOT(ISBLANK('Employees'[EmpName])),

NOT(ISBLANK('Employees'[EmpLastName]))

),

NOT(ISBLANK('Employees VIP'[Status VIP]))

),

NOT(ISBLANK('Employees TIC'[Status TIC]))

)

),

"'Employees'[EmpName]", 'Employees'[EmpName],

"'Employees'[EmpLastName]", 'Employees'[EmpLastName],

"'Employees VIP'[Status VIP]", 'Employees VIP'[Status VIP],

"'Employees TIC'[Status TIC]", 'Employees TIC'[Status TIC]

)

VAR __DS0PrimaryShowAllCompat =

FILTER(

KEEPFILTERS(

GENERATEALL(

KEEPFILTERS(

FILTER(

KEEPFILTERS(VALUES('Employees'[EmpName])),

OR(

CALCULATE(NOT(ISEMPTY('Employees'))),

CALCULATE(NOT(ISEMPTY('Employees VIP')))

)

),

CALCULATETABLE(

GENERATEALL(

KEEPFILTERS(VALUES('Employees'[EmpLastName])),

CALCULATETABLE(

GENERATEALL(

KEEPFILTERS(

FILTER(

KEEPFILTERS(VALUES('Employees VIP'[Status VIP])),

CALCULATE(NOT(ISEMPTY('Employees VIP')))

)

),

CALCULATETABLE(

FILTER(

KEEPFILTERS(VALUES('Employees TIC'[Status TIC])),

CALCULATE(NOT(ISEMPTY('Employees VIP')))

)

),

OR(

NOT(ISBLANK('Employees'[EmpName])),

NOT(ISBLANK('Employees'[EmpLastName]))

),

NOT(ISBLANK('Employees VIP'[Status VIP]))

),

NOT(ISBLANK('Employees TIC'[Status TIC]))

)

VAR __DS0PrimaryShowAll =

SUMMARIZE(

UNION(__DS0Core, __DS0PrimaryShowAllCompat),

'Employees'[EmpName],

'Employees'[EmpLastName],

'Employees VIP'[Status VIP],

'Employees TIC'[Status TIC]

)

VAR __DS0PrimaryWindowed =

TOPN(

501,

__DS0PrimaryShowAll,

'Employees'[EmpName],

1,

'Employees'[EmpLastName],

1,

'Employees VIP'[Status VIP],

1,

'Employees TIC'[Status TIC],

1

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

'Employees'[EmpName],

'Employees'[EmpLastName],

'Employees VIP'[Status VIP],

'Employees TIC'[Status TIC]

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696