Forum Discussion

PBILix's avatar
PBILix
Frequent Visitor
1 year ago

Analyze in Excel Null Values

Hello Community,

 

I am using my semantic model in Analyze in Excel. I load Data from Table A and Table B. Before Loading the data, I filter in the analyze in Excel Editor for some Values of Column A. The default query code for this is using TREATAS. 

 

My problem is, that in Excel automatically all Rows are filtered out, which do not have a value in Table B. Even if there are Values in Table A. I need to display also the Values of Table A, which do not of corresponding Data in Table B.

 

Unfortunately I inherited the model and it is using a one to many form Table A to Table B Bidirectional Filtering, which i cant change for the moment. 

 

In Power Bi itself, I will receive all necesary rows in a table visual, just in analyze in excel it is filtered out. 

 

DEFINE
VAR __DS0FilterTable = 
TREATAS(
{ "...", "..."},
 
'A'[C] // C is the Column which is filtered by of Table A
)
 
 
 
VAR __DS0Core = 
SUMMARIZECOLUMNS(
'A'[A],
'B'[B]
)
 
VAR __DS0BodyLimited = 
TOPN(
500000,
__DS0Core,
'A'[A],
1,
'B'[B],
1)
 
EVALUATE
__DS0BodyLimited
 
ORDER BY
'A'[A],
'B'[B],
 
Need Help

2 Replies

  • PBILix's avatar
    PBILix
    Frequent Visitor
    1 year ago

    When I put Table A Column A in the Editor and filter by one of the specified Values which are disappearing later, it appears. 

     

     

    When I add a Column of Table B, Also the value in column a is blank.

     

    Probably has to do with the handling of null values in the relation between the tables..

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, PBILix 

    I am glad to help you.

     

    You need to make sure that the TREATAS function handles null values correctly. You may need to adjust the filtering logic so that rows in Table A are included even if there is no corresponding row in Table B.

     

    In SQL, you would normally use a LEFT JOIN to include all the rows in table A as well as the matching rows in table B. You can use ADDCOLLIN in DAX. You can use the ADDCOLUMNS and RELATED functions in DAX to simulate this behavior.

    VAR __DS0Core = 
ADDCOLUMNS(
    'A',
    "B_Column", RELATED('B'[B])
)

     

    Since you mentioned that the model uses one-to-many bi-directional filtering, make sure that the relationship settings are configured correctly. Sometimes changing the direction of cross-filtering to unidirectional can help with this situation.


    If possible, check the data model to make sure the relationships and data types are properly defined. Sometimes, null value issues can stem from data type mismatches or incorrectly defined relationships.

     

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.