How to use TREATAS to create a virtual relationship for a virtual table, in use of a Measure.

[Posvse]

I trying to create a Measure to be used in a Matrix Visual in a report, but I can't get the values correct for the rows in the matrix.

Data is coming from a Jira system, see image of the Data Model.
Data model 

The Measure I'm trying to create have the following requirements:
* Count all Jira tickets/rows (one table row per ticket) in table "Jira Data".
* Exclude the Jira tickets that have a specified list of Components assigned.
* Exclude the Jira tickets that haven't a value specified for the Customfield ("Jira Data_customfield.Customfield Value" is blank)

The Matrix has "Team Name" (see table "Development Team") as columns and "Customfield Value" (see table "Jira Customfield Values") as rows. My Measure that I'm trying to create is used as value in the Matrix visual.

The reason for using "Jira Customfield Values.Customfield Value" is that I want to have all available values included as rows in the Matrix, and I can use the "Sort Order" values to get a nice sorting in the Matrix. If there are Jira tickets that haven't any of the defined customfield value, my measure should return 0 as number.
If I would use "Jira Data_customfield.Customfield Value" as row, I wouldn't be getting all possible rows values ("XS", "S", "M", "L" and "XL") since there might not be Jira tickets using all values, and I wouldn't be able to set the correct sort order.

The issue now is that I can create a measure that calculate according to the above requirements. But since there isn't any direct relatonship between the "Jira Data_customfield.Customfield Value" and "Jira Customfield Values.Customfield Value", I get the "classical" issue with the total measure value per team is displayed in each Matrix row, see image.
Matrix screenshot 

This issue can of course be solved by using the TREATAS function and create a virtual relationship between the columns. But this is where I get stuck in DAX. I can't figure out how to, and where to add the TREATAS function.

I can solve this by creating a new table in Power Query just for this Matrix visual, but it seems wrong from a design point of view.

My measure is defined using the below DAX formula. In this case I've tried to create a virtual table, with all valid Jira tickets as rows. The total sum is correct, but I can't find a way to add a virtual relationship for my virtual table, which messes up the Matrix Values.

If you have suggestion on changes of the Data model, you're welcome to suggest those, if that can solve the above issue easier.

Functional Measure =
VAR VirtualTable =
    NATURALINNERJOIN (
        SUMMARIZE (
            FILTER (
                'Jira Data_Components',
                NOT (
                     CONTAINSSTRING ( 'Jira Data_Components'[Component Name List], "Infrastructure" )
                  || CONTAINSSTRING ( 'Jira Data_Components'[Component Name List], "TA " )
                )
            ),
            'Jira Data_Components'[Component Name List],
            'Jira Data'[Key]
        ),
        SUMMARIZE (
            FILTER (
                'Jira Data_customFields',
                NOT ( ISBLANK ( 'Jira Data_customFields'[Customfield Value] ) )
            ),
            'Jira Data_customFields'[Customfield Value],
            'Jira Data'[Key]
        )
    )
VAR TotalWeight =
    CALCULATE ( COUNTROWS ( VirtualTable ) )
VAR Result =
    IF ( TotalWeight > 0, TotalWeight, 0 )
RETURN
    Result

[Posvse · Accepted solution]

I think I've solved it by using the following DAX code for my Measure (in case someone google this thread). This DAX code returns the correct count in the Matrix visual, in the cells and in the total sum row.

Functional Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Jira Data'[Key] ),
    TREATAS (
        SUMMARIZE (
            FILTER (
                'Jira Data_customFields',
                NOT ( ISBLANK ( 'Jira Data_customFields'[Customfield Value] ) )
            ),
            'Jira Data_customFields'[Key],
            'Jira Data_customFields'[Customfield Value]
        ),
        'Jira Data'[Key],
        'Jira Customfield Values'[Customfield Value]
    ),
    TREATAS (
        SUMMARIZE (
            FILTER (
                'Jira Data_Components',
                NOT (
                    CONTAINSSTRING ( 'Jira Data_Components'[Component Name List], "Infrastructure" )
                 || CONTAINSSTRING ( 'Jira Data_Components'[Component Name List], "TA " )
                )
            ),
            'Jira Data_Components'[Key]
        ),
        'Jira Data'[Key]
    )
)

[Posvse]

@Anonymous 
Thanks for the link. I've read it earlier, but I'm still not sure how to include the TREATAS function in the DAX formula for my measure.
Should it be included in the "VirtualTable" VAR definition or in the "TotalWeight" calculation? Or should the whole measure calculation be reconfigured?

[Posvse]

Here's another try I made, but with the same result. The measure isn't filter based on the rows "XS", "S", "M" etc.

Functional Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Jira Data'[Key] ),
    TREATAS (
        SUMMARIZE (
            FILTER (
                'Jira Data_customFields',
                NOT ( ISBLANK ( 'Jira Data_customFields'[Customfield Value] ) )
            ),
            'Jira Data_customFields'[Key],
            'Jira Data_customFields'[Customfield Value]
        ),
        'Jira Data'[Key],
        'Jira Customfield Values'[Customfield Value]
    ),
    TREATAS (
        SUMMARIZE (
            FILTER (
                'Jira Data_Components',
                NOT (
                    CONTAINSSTRING ( 'Jira Data_Components'[Component Name List], "Infrastructure" )
                        || CONTAINSSTRING ( 'Jira Data_Components'[Component Name List], "TA " )
                )
            ),
            'Jira Data_Components'[Key]
        ),
        'Jira Data'[Key]
    )
)

[Anonymous]

Hi@Posvse,

Here is about what the TreatAs function is and how it can help to build filters and a virtual relationship between two tables. 

Please check if this article is helpful:Building-a-virtual-relationship-in-power-bi-basics-of-treatas-dax-function  

Hope it helps.

Best Regards,
Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.