Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I'm sorry in advance if there is an easy solution that I'm just not finding. I'm working on a very high priority Dashboard project and have spent 2 days trying to figure out how to make this work. I need to have something to show them by the end of the week.
I have two issues that I just can't figure out.
ISSUE 1:
I created a table to only have specific columns from an imported sql table. This is so I can use this in an EXCEPT table later:
The other table (Travel Claims Log) I used in the EXCEPT table has this data: (note: 61853 is the first record and lowest number in this table)
Solved! Go to Solution.
Issue 1:
EXCEPT ( <LeftTable>, <RightTable> )
it excludes from LeftTable the rows also existing in RightTable; which means all columns of a row, i.e. VPT, DATE, EMP NAME and AMOUNT in your case, are taken into consideration. At a glimpse, for row 'Travel Claims Log'[VPT #]=61854, 'Travel Claims Log'[AMOUNT]=477.45 doesn't exist in 'RStars Travel', thus this row is kept.
Issue 2,
RStars Travel =
SELECTCOLUMNS(
FILTER(
RStarsTravelReport,
NOT (
LEFT( RStarsTravelReport[RSTravel_VPTNbr] ) = "C"
|| LEFT( RStarsTravelReport[RSTravel_VPTNbr], 2 ) = "LT"
)
),
"VPT #", RStarsTravelReport[RSTravel_VPTNbr],
"Date", RStarsTravelReport[Final_Approv_Dt],
"Employee Name", RStarsTravelReport[Vendor_Name],
"Amount", MAX( RStarsTravelReport[Sfms_Trans_Amt], 0 )
)
filter out undesired rows with specific symbols upon creating the new table.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Issue 1:
EXCEPT ( <LeftTable>, <RightTable> )
it excludes from LeftTable the rows also existing in RightTable; which means all columns of a row, i.e. VPT, DATE, EMP NAME and AMOUNT in your case, are taken into consideration. At a glimpse, for row 'Travel Claims Log'[VPT #]=61854, 'Travel Claims Log'[AMOUNT]=477.45 doesn't exist in 'RStars Travel', thus this row is kept.
Issue 2,
RStars Travel =
SELECTCOLUMNS(
FILTER(
RStarsTravelReport,
NOT (
LEFT( RStarsTravelReport[RSTravel_VPTNbr] ) = "C"
|| LEFT( RStarsTravelReport[RSTravel_VPTNbr], 2 ) = "LT"
)
),
"VPT #", RStarsTravelReport[RSTravel_VPTNbr],
"Date", RStarsTravelReport[Final_Approv_Dt],
"Employee Name", RStarsTravelReport[Vendor_Name],
"Amount", MAX( RStarsTravelReport[Sfms_Trans_Amt], 0 )
)
filter out undesired rows with specific symbols upon creating the new table.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@SandeA
When you use EXCEPT, it matched columns from both tables by positions, and columns are compared
"If a row appears at all in both tables, it and its duplicates are not present in the result set. If a row appears in only table_expression1, it and its duplicates will appear in the result set."
Regarding the Issues # 2 yes it is possible.
Can you share a PBIX file with sample data with the scenario and the expected results?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
92 | |
88 | |
88 | |
82 | |
49 |
User | Count |
---|---|
156 | |
145 | |
104 | |
72 | |
55 |