Forum Discussion
Question about Filters and variables in PowerBI
Good afternoon, I have been working on a powerbi measure for the last few days that would check the count of a date field, and filters based on specific values in other tables columns.
The issue I'm facing is when using the variables for the max Ref_doc_num, and Max Source|purdocNum|PurdocItemnum fields. To give some background there exists a relationship between supplierschagreement and SAPLVMIN but it is a many to many relationship on the source field. (SAPLVMIN is a calculated table and based off of another table we have pulling into our dataset)
When I add in the if clause of where the REFDOCNUM = SCHLINECOUNTER and the two sources being equal as well it causes a blank in my check table for this measure however I know there are cases where the two fields should be equal. The measure works fine without this equality check. What I wanted to ask the community here about is if its possible to do an equality check like the below between two preset variables in DAX? I have never directly compared two variables like this before, but I couldn't seem to find another option. Is there a better way to compare two columns directly that reside within different tables in DAX?
Dax Formula:
Relationship Example:
- Anonymous2 years ago
Hi sleopol
It seems that you want to create a link between two table which relationship can only be many-to-many.
Maybe you can consider the crossjoin() function to create a new table and use the filter() function to filter the value you need.
Here is a sample:
Here are two table:
Then create a new table:
Table = FILTER(CROSSJOIN('ORDER','QT'),'ORDER'[ID]='QT'[_ID])The result is as follow:
It is worth noting that these two table can't have the same column name.
Link of crossjoin():
CROSSJOIN function (DAX) - DAX | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- Wilson_
Memorable Member
sleopol,
Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂
- AnonymousNot applicable
Hi sleopol
It seems that you want to create a link between two table which relationship can only be many-to-many.
Maybe you can consider the crossjoin() function to create a new table and use the filter() function to filter the value you need.
Here is a sample:
Here are two table:
Then create a new table:
Table = FILTER(CROSSJOIN('ORDER','QT'),'ORDER'[ID]='QT'[_ID])The result is as follow:
It is worth noting that these two table can't have the same column name.
Link of crossjoin():
CROSSJOIN function (DAX) - DAX | Microsoft Learn
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.