I am using a Live connection to an SSAS tabular model having the following tables : Employees : all Employees in the companyEmployees VIP : Employees having the status of VIP in the companyEmployees 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 EmpIDEmployees - 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, EmpLastNameEmployees VIP : Status VIPEmployees 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 :

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

Show items with no data is not giving expected result

9 Replies

parry2k
Super User
2 years ago
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.
- AmiraBedh
  Super User
  2 years ago
  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(
  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 __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(
  OR(
  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(
  OR(
  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]
parry2k
Super User
2 years ago
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?
- AmiraBedh
  Super User
  2 years ago
  You can find the link to Google Drive : https://drive.google.com/drive/folders/1-rk-VKwYrGdbY7q_cjk_3TlBbAtH3U-H?usp=sharing

parry2k

Super User

2 years ago

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

AmiraBedh
Super User
2 years ago
So it is a normal behavior?
How to achieve what I want in this case ?

parry2k
Super User
2 years ago
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
- AmiraBedh
  Super User
  2 years ago
  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 ?
AmiraBedh
Super User
2 years ago
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

Forum Discussion

Show items with no data is not giving expected result

9 Replies

Related content

Distinct Filter not giving the result expected

SAMEPERIODLASTYEAR not giving correct results

Userelationship not giving expected results

Dax Query not giving results as expected

AVERAGEX not giving expected result