Count Concatenate Measure

[Aleiadeh]

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.

[Anonymous · Accepted solution]

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 & " - " & _MAXVALUE

COUNT =
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.