Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
Edit: Proving more informations
I have this two different tables, the fact table (many ids) and my time table (unique id)
TABLE 1 FACT
TimeID | ID | start time | finish time |
1 | A | 11:00 AM | 11:15 AM |
1 | A | 12:00 PM | 12:15 AM |
2 | B | 5:00 PM | 5:15 PM |
3 | C | 7:00 PM | 9:00 PM |
TABLE 2 TIME TABLE
TimeID | Time | HOUR |
1 | Morning | 11:00 AM |
2 | Afternon | 12:00PM |
3 | Evening | 6:00 PM |
My result is duplication ID A because I have ID A for Morning and Afternon, however I need to use only the first value which is morning.
TimeID | Time | ID |
1 | Morning | A |
1 | Afternon | A |
2 | Afternoon | B |
3 | Evening | C |
The result I need
TimeID | Time | ID |
1 | Morning | A |
2 | Afternoon | B |
3 | Evening | C |
I need that the ID (A) only appears once for morning which is where the it starts.
I need to use dax calculated measure for this, is this possible?
Thanks in advance
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
hI @Anonymous ,
You can try this formula to be used as a visual filter where value = 1
FirstValue =
IF (
SELECTEDVALUE ( 'FACT'[start time] )
= CALCULATE ( MIN ( 'FACT'[start time] ), ALLEXCEPT ( 'FACT', 'FACT'[ID] ) ),
1,
0
)
Proud to be a Super User!
I just provided more details!
thank you!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share the base data tables from where you created this output.
Hello @Anonymous ,
The output required is not clear from your description. Please share more details on the output.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
It is possible, but not the way you have your data. If I use the MIN() function, I get this:
MAX() would have returned Morning for ID1, but the problem is Power BI doesn't know what "Morning" and "Afternoon" are. Instead, MIN/MAX are working alphabetically here.
And since your data isn't alphabetical, MIN/MAX won't work. For example, MIN returns afternoon in this example, but if you had Morning and Evening for some IDs, MIN there would return Evening because E is before M. You need another field to define the order to take your data and pull that. Or convert Morning, Afternoon, Evening into 8am, 12 noon, 6pm as a time field. Then MIN/MAX would work fine.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportinghI @Anonymous ,
Does your table indicate which value in Time column comes first for each ID? You may create a formula that returns only the value for Morning but Morning may not alwasy be the first value.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |