Related tables, but no common key - how to drillthrough?

[SeniorJunior]

Hi all, 

I have two tables that are related in content, but without a field. 

One table has a list of all the column names under the value 'field' whereas the other one has the details behind all of these values - what I would like to do is, list out the field column (which then shows the name of each column as part of the table) and then using this carry out a range of calculations on the values but also allowing for drillthrough to the more detailed table so when I say that there's 14 values in a particular column, I can drill through and see what these are. 

It could be that I am approaching this wrong and there's an easier way to list out column names in a table format, so I am happy to learn or expand on this further. 

As an example of the tables I would have

Summary table

Field
Name
Job Role

Detail table

Name	Job Role
A	Set
B	Set
C	Contract
D	Set
E	Ended
F

So I would have an outcome of a table visual that would say

Field	Total Values
Job Role	5

And I could then drill down through that to see those five entries.

[SeniorJunior]

Thanks - this works on a small scale, but the detail table will end up with 30+ columns so it will quickly become unmanageable to unpivot due to the size involved. 

I wonder if I need to take a different approach and work within the detail table to produce this list of field names (if possible) and that way the drillthrough and related measures would be easily done.  

[v-jtian-msft]

Hi,@SeniorJunior .Thank you for your reply.
In fact it is as you wrote in the title. The actual data is now logically related, but you can't actually create the relationship because the fields don't match (which is the root cause of my field parameters and inverse pivot tables)
power bi needs to be able to drill down across pages only if there is a connection between the tables. Otherwise it is more difficult for you to realize your needs directly through dax. And power bi dax does not have a function to get the column name information of the table (this is at the architectural level, whereas dax is suitable for dealing with the data logic)

Solved: Is there a  DAX command for retrieving a column na... - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jtian-msft]

Hi,@SeniorJunior .I am glad to help you.
Like this?

When I click on the table where the measure is located I can drill down to another page to see more detailed information.

You may need to use them:

You may need to use
filed  parameter,
Unpivot columns
and measure drill through

Use report readers to change visuals (preview) - Power BI | Microsoft Learn
Unpivot columns - Power Query | Microsoft Learn
Set up drillthrough in Power BI reports - Power BI | Microsoft Learn
 

this is my test
1.create a field parameter
 

The slicer only passes the filtered data, it doesn't have a filtering effect itself.
We need to control the final output by the value of measure.

In power bi, measure must select the corresponding column in order to calculate the aggregation of the column, so we need to process the original data.
By reverse pivoting the data and calculating the correct result based on the grouping
I made a new copy of the table and reverse pivoted the table (which I then needed to use to create the measure, and after reverse pivoting, the fields could be related by [filed] fields)

the measure I create:

FieldCount = 
VAR _selected =
    MAX ( 'Parameter'[Parameter] ) //Record the selected value in the slicer
RETURN
    CALCULATE (
        COUNT ( 'UnpivotedDetail table'[Attribute] ),
        FILTER (
            'UnpivotedDetail table',
            'UnpivotedDetail table'[Attribute] = _selected
                && 'UnpivotedDetail table'[Value] <> BLANK ()
        )
    )

Once measure is working properly, finally we need to create a drill through page.
Place the same measure in the drilldown page, and then place the common measure in the drilldown area to realize the page drilldown effect.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.