Forum Discussion
Count Concatenate Measure
Hello,
Please I need your support to find a solution for me on the following issue. I will try to expadite my explnation and what I need as much as I can:
I have a Bid Status Table updated on weekly based, hereunder the structure of the table:
- Table(1):
| Bid No. | 1/1/2021 | 8/1/2021 | 15/1/2021 | 22/1/2021 | 29/1/2021 |
| Bid-001 | Bid Preparation | In Market | In Market | Bid Opening | Awarded |
| Bid-002 | Bid Preparation | In Market | Bid Opening | Awarded | Awarded |
| Bid-003 | Bid Preparation | Awarded | Awarded | Awarded | Awarded |
| Bid-004 | Bid Preparation | Awarded | Awarded | Awarded | Awarded |
| Bid-005 | Bid Preparation | Bid Preparation | In Market | In Market | In Market |
So now, If I inserte Date Slicer ( between two dates) after unpivot the table and select the period from 8/1/2021 To 22/1/2021, then the result that I need after concatenate First and Last Date Status will be:
- Table(2):
| Bid No. | Status Changes based selected dates from 8/1/2021 To 22/1/2021 |
| Bid-001 | In Market - Bid Opening |
| Bid-002 | In Market - Awarded |
| Bid-003 | Awarded - Awarded |
| Bid-004 | Awarded - Awarded |
| Bid-005 | Bid Preparation - In Market |
Now The most important step is to count the distinct changes:
- Table(3):
| Status Changes | Count |
| In Market - Bid Opening | 1 |
| In Market - Awarded | 1 |
| Awarded - Awarded | 2 |
| Bid Preparation - In Market | 1 |
Actually I did all steps as per the below photo but I didn't know how to get Table(3)!
Could you please help me to solve this issure.
If any explanation is not clear please let me know.
Thank you in advace.
Hi Aleiadeh ,
It's impossible to achieve such a table visual with only measures. It will return a single row like this:
And you want to select date dynamically via slicer, so it is impossible to create a calculated table as well.
Please try this:
Status Changes = VAR _MINDATE = MINX ( 'Table(1)', 'Table(1)'[Attribute] ) VAR _MAXDATE = MAXX ( 'Table(1)', 'Table(1)'[Attribute] ) VAR _MINVALUE = CALCULATE ( MAX ( 'Table(1)'[Value] ), FILTER ( 'Table(1)', 'Table(1)'[Attribute] = _MINDATE ) ) VAR _MAXVALUE = CALCULATE ( MAX ( 'Table(1)'[Value] ), FILTER ( 'Table(1)', 'Table(1)'[Attribute] = _MAXDATE ) ) RETURN _MINVALUE & " - " & _MAXVALUECOUNT = VAR _T = SUMMARIZE ( ALLSELECTED ( 'Table(1)' ), 'Table(1)'[Bid No.], "NEW", [Status Changes] ) VAR _A = [Status Changes] RETURN COUNTAX ( FILTER ( _T, [NEW] = _A ), [NEW] )Please add at least one column (Bid No) to the visualization.The final output is shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Aleiadeh ,
It's impossible to achieve such a table visual with only measures. It will return a single row like this:
And you want to select date dynamically via slicer, so it is impossible to create a calculated table as well.
Please try this:
Status Changes = VAR _MINDATE = MINX ( 'Table(1)', 'Table(1)'[Attribute] ) VAR _MAXDATE = MAXX ( 'Table(1)', 'Table(1)'[Attribute] ) VAR _MINVALUE = CALCULATE ( MAX ( 'Table(1)'[Value] ), FILTER ( 'Table(1)', 'Table(1)'[Attribute] = _MINDATE ) ) VAR _MAXVALUE = CALCULATE ( MAX ( 'Table(1)'[Value] ), FILTER ( 'Table(1)', 'Table(1)'[Attribute] = _MAXDATE ) ) RETURN _MINVALUE & " - " & _MAXVALUECOUNT = VAR _T = SUMMARIZE ( ALLSELECTED ( 'Table(1)' ), 'Table(1)'[Bid No.], "NEW", [Status Changes] ) VAR _A = [Status Changes] RETURN COUNTAX ( FILTER ( _T, [NEW] = _A ), [NEW] )Please add at least one column (Bid No) to the visualization.The final output is shown below:
Here is the pbix file.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.