Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Im creating a report based on a "table" visual (not a matrix) . This visual has following columns (+ 10 others that are not important for this case). The report is basically a big dump of specific data that will be exported later. Below an example
Now when there are 2 "duplicate" id's, it means that there has been a change somewhere in a column that is not part of this visual. These changes are tracked by the modifcation date.
I use the measure:
so result should be as below: But the "Last date column" should not be visible. This is created by a measure that i use on the table
So I need to select a ticket id based on the most recent modification date. This way every ticket id is unique in the visual. Whats the easiest way to do this, without showing the measure as a column in the table?
No option to add a calculated column, I want only to have the columns visible that are supposed to be visual in the table. this for export reasons
original data for this table will be refreshed from a odatafeed, and contains a lot more records then these 4 obviously 😉
Hi @TechR21 ,
You can try below:
Please mark it as answer If you find it useful.
this works ! i made a mistake when applying this but got it working, thanks! question; will this work when columns are in two different tables?
so for example ticketid is in table tickets, and modificationdate is in table ticketdetails
Yes, it will, the two tables will have to be related for that to work.
and measure will be slightly modified. New measure will look like:
The only "problem" I see is the linking of the tables, because i need the ticketnumber in my visual, I put for some reason the ticketid in the visual.
The table "tickets" has ticketnumber, and an id.
The table "ticketdetails" has modificationdate
the tables ("tickets"and "ticketdetails") are linked by id(in tickets) and ticketid (in ticketdetails)
So to use your query i need to make the link based on link between the two tables but the calculation needs to be done on the ticketnumber, but this I cant seem to make it work in your last query
If your relationship is based on ticket ID then also it will work.
since Ticket Details table will be your child to tickets main table, with a many-to-one relationship (ticket details (many) to tickets main (one)), you can add a DAX column into ticket details fetching RELATED(TicketNumber) and in above query replace ticketid by ticket number,
I believe that will work
If you want it by unique ticket ID you use ticket Id, If you want it by unique ticket number, you use ticket number.
Still you can put as many columns as you want on the table visual and paint them in white so as to make them hidden and set the width to smallest.
yes the relationship is already there, via the Model. What I try to do is include this in your last query, because the previous one was working, and i can easily apply this measure as a filter. But Im not sure how to put the link between the two tables in the formula
i dont use a matrix, but table visual. Also no extra column in the visual is allowed, due to exports of the report. Updated my initial question for clarity
i dont use a matrix, but table visual. Also no extra column in the visual is allowed, due to exports of the report. Updated my initial question for clarity
@TechR21 , refer if these two blogs can help
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
I see in your blog, you make use of 3 columns? i tried following measure but when i apply to my table visual the whole visual fails to give back any data
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |