The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table where it looks like this
c1 | c2 | date |
A1 | B1 | 10-10-24 |
A2 | B2 | 10-10-24 |
A3 | B3 | 10-10-24 |
A2 | B2 | 11-10-24 |
A4 | B4 | 11-10-24 |
So, in this example, A1-B1 and A3-B3 were removed from the table during the month of November from October
and A4-B4 was added to the table.
I want two tables which show me these 2 outputs.
I use two columns to identify any unique row c1-c2.
How do I go about doing this?
Thank you in advance.
Solved! Go to Solution.
Hi @afaro ,
I create a table as you mentioned.
Then I create a calculated column.
Column = 'Table'[c1] & "-" & 'Table'[c2]
I create a measure and here is the DAX code.
Measure =
CALCULATE (
COUNT ( 'Table'[Column] ),
FILTER (
ALL ( 'Table' ),
'Table'[Column]
IN VALUES ( 'Table'[Column] )
&& 'Table'[date] = "10/10/2024"
)
)
- CALCULATE (
COUNT ( 'Table'[Column] ),
FILTER (
ALL ( 'Table' ),
'Table'[Column]
IN VALUES ( 'Table'[Column] )
&& 'Table'[date] = "11/10/24"
)
)
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @afaro ,
I create a table as you mentioned.
Then I create a calculated column.
Column = 'Table'[c1] & "-" & 'Table'[c2]
I create a measure and here is the DAX code.
Measure =
CALCULATE (
COUNT ( 'Table'[Column] ),
FILTER (
ALL ( 'Table' ),
'Table'[Column]
IN VALUES ( 'Table'[Column] )
&& 'Table'[date] = "10/10/2024"
)
)
- CALCULATE (
COUNT ( 'Table'[Column] ),
FILTER (
ALL ( 'Table' ),
'Table'[Column]
IN VALUES ( 'Table'[Column] )
&& 'Table'[date] = "11/10/24"
)
)
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works but isn't dynamic though. I wanted user to be able to choose month/year
Use the date as a filter. In fact, you should have a calendar table for that.
Read about EXCEPT and INTERSECT.
My data is in the same table and not separate tables.
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |