summary table with continuous count of status dupl...

Melmehal · ‎09-13-2019

Hi ALL,

I have a quotelog table that shows different status for each quote. Sometimes this status can be repeated for each quote. in tbe below image i filtered just one "quote" to demonstrate this. I want to be able to see for each "quote" if it had more than 1 count of a status. Im not sure how this would be designed. column or a measure. but simply put in the below image i want a summary that says

Quote Number of times status 2 occured Number of times status 6 occured

84848 2 2

The key thing is that i need this to be unaffected by my date filter in the report. its also imporant that i know which status each count is for.

parry2k · ‎09-13-2019

@Melmehal use matrix visual,

- Add quote on rows

- Add status on columns

- Add Id on values and change aggregation to Count

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Melmehal · ‎09-13-2019

That works, however i need to use this as a filter. Here is what each status number means:

I want to be able to on a daily basis track the numbers of quotes where it had status 7 (broker gets estimate). but the issue i run into is that this status 7 can appear multiple times and i only want it to count once ( the first time in which it occured). You might suggest that i should remove my duplicate statuses for each quote and only keep the first instance. This isnt the best solution because if you notice in my initial post, status 2 ( assigned to broker) also occured twice, if i were to only keep the first instance, it would be assigned to the wrong person.

The main question becomes: How can i keep the duplicate statuses of each quote and still be able to count for each quote a distinct count of each status.

so on any given day i want to filter my report and count for each broker how many quotes went through "status 7" on that given day. Say, today a broker took a quote to status 7 ( i only want to count this status once for each quote) BUT he took the same quote to "status 7" 5 days ago, then i dont want it to count for today, because it would have been counted 5 days ago and is now simply a duplicate

perhaps what i need is a version of this table which shows only the last instance of each quote's status (which i have already done) and use the main table that has the duplicate statuses as a filter where the count of each status occurence for each quote is NOT greater than 1