Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have 2 tables.....one is Calendar table and another is maindatabase.......in maindatabase table.....I have 3 columns.......
1. Items
2. Received date
3. Expiration date
Received date has primary relationship with Date column of calendar table.......Expiration date has secondary relationship with date column of Calendar table.......Now I want to count the distinct items.....basis the months and want to know they are expiring in which month.....so for an example......the woids that I received in the month of Jan are expiring in which months of year....similarly the woids that I received in the month of Feb are expiring in which month of year.....can anyone please help......
Solved! Go to Solution.
@sandeep_sharma There may be other methods, but I solved this using 2 calendar tables and the following measure. PBIX is attached below signature.
Measure =
VAR __Received = MAX('Calendar'[MonthSort])
VAR __Expired = MAX('Expiration Calendar'[MonthSort])
VAR __Table = FILTER(ALL('Table'), MONTH( 'Table'[Received date] ) = __Received && MONTH( 'Table'[Expiration Date] ) = __Expired )
VAR __Products = DISTINCT( SELECTCOLUMNS( __Table, "__Item", [Item] ) )
VAR __Result = COUNTROWS( __Products )
RETURN
__Result
@sandeep_sharma You can use USERELATIONSHIP coupled with CALCULATE or CALCULATETABLE such as:
Measure =
VAR __Table = CALCULATETABLE( 'maindatabase', USERELATIONSHIP( 'Calendar'[Date], 'maindatabase'[Expiration date] )
VAR __Products = DISTINCT( SELECTCOLUMNS( __Table, "__ProductID", [ProductID] ) )
VAR __Result = COUNTROWS( __Products )
RETURN
__Result
Otherwise, please provide sample data and expected results.
Not sure if I can insert a file here....hence sharing the Snapshot of sample data......
Also, below is what I need,
Please be noted that I have a calendar table that has primary relationship with Received date column and secondary relationship with expiry date.......not sure what is the best way to show.....when I dont use calendar table and put received date in rows and expiry date in column....they dont show the hierarchy even when they have right data format.......
@sandeep_sharma Any chance you can post that sample data as text so I can copy and paste? I don't want to type all that.
Item | Received date | Expiration Date |
2636683 | 3/4/2024 | 3/4/2024 |
3792176 | 5/6/2024 | 6/5/2024 |
2054262 | 2/4/2024 | 3/5/2024 |
1761226 | 2/5/2024 | 3/6/2024 |
4884335 | 2/4/2024 | 4/4/2024 |
2318785 | 3/7/2024 | 4/6/2024 |
4548830 | 3/8/2024 | 4/7/2024 |
2607540 | 3/4/2024 | 4/3/2024 |
3100589 | 3/4/2024 | 4/3/2024 |
1570941 | 5/4/2024 | 5/4/2024 |
1228598 | 5/6/2024 | 6/5/2024 |
4356847 | 5/7/2024 | 6/6/2024 |
4855698 | 3/4/2024 | 4/3/2024 |
1895040 | 2/7/2024 | 3/8/2024 |
3904926 | 2/8/2024 | 2/8/2024 |
1252931 | 2/8/2024 | 2/8/2024 |
4007366 | 2/10/2024 | 3/31/2024 |
4610045 | 5/7/2024 | 6/6/2024 |
3020933 | 5/7/2024 | 6/6/2024 |
2150199 | 3/4/2024 | 4/3/2024 |
@sandeep_sharma There may be other methods, but I solved this using 2 calendar tables and the following measure. PBIX is attached below signature.
Measure =
VAR __Received = MAX('Calendar'[MonthSort])
VAR __Expired = MAX('Expiration Calendar'[MonthSort])
VAR __Table = FILTER(ALL('Table'), MONTH( 'Table'[Received date] ) = __Received && MONTH( 'Table'[Expiration Date] ) = __Expired )
VAR __Products = DISTINCT( SELECTCOLUMNS( __Table, "__Item", [Item] ) )
VAR __Result = COUNTROWS( __Products )
RETURN
__Result
thanks @Greg_Deckler . This is very innovative solution. I saw this first time and got to learn a new thing.
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
119 | |
41 | |
40 | |
28 | |
22 |