Table set-up - Union into one table

[aashton]

Hello,

I have an Applications table which includes Date, ID, Email, Name, Source etc.  I also have a Contracts table which shows similar fields, Date, ID, Email, Name and Source.  All the the visuals will revolve around source, how many contracts and applications by source.  To do this I unioned them into one table (I don't know if this was the best idea), like this:  Table (Applications or Contracts), Date, ID, Email,Name Source.  I then created two measures for Total Applictions and Total Contracts and use these in Clustered Column charts. 

Total Application LAC = CALCULATE(DISTINCTCOUNT('Lead Application Contract Sources'[Index]), 'Lead Application Contract Sources'[Table]="Applications")

Total Contracts = CALCULATE(DISTINCTCOUNT('Lead Application Contract Sources'[Candidate ID]), 'Lead Application Contract Sources'[Table]="Contracts")

Everthing looks good but I need to be able to drill-through to show which applications or contracts make up each source, but it's not working.   For example, if I drill-through image below Total Applications GasWorks 243, the details page gives me both applications and contracts (the same ID, Name, Email can also be in both tables) exceeding the 243.  I  am doubting my whole set-up now??  Is there a better way to show total applications and contracts by source in the same visual?

[v-yueyunzh-msft]

Hi， @aashton 

 According to your description,  you have two tables with the same structure .And you have unioned them to one table .Then you create two measures to as the Y-axis and when you drill through the visual , the value can not be filtered.

For your need , i see the dax code you create is used the differnt field to get the count .

 

I think that if you have two tables with the same field, then you don’t need to separate two fields to calculate the count, you can directly put them in the same column to evaluate, and you don’t need to create a measure,like this table:

And then we can put the field on the visual like this:

And then we can configure the drill-through page like this:

Then we can drill through from page 1 to page 2. 

Because the data I guess may be different from your actual data, if this can't help you solve the problem, can you provide me with a sample .pbix (without sensitive data) for testing(You can upload the sample .pbix file to OneDrice and share the link to me )? This can help you solve the problem faster.

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

And, when I drill-through to the details page, is there a way to show the ID, Name, Application Source and Contracts Source like below?  So for example, if I do drill-through on all of Agency applications, I will see the list of applications, and if that ID does have a Contract, it will also list the Contract Source.  Since it is filtering on Table it is only showing one or the other.  

[v-yueyunzh-msft]

Hi , @aashton 

Thanks for your quick response!

This is my understand for your question:

(1)For the matrix , if you have other measures before,So you need to rewrite these measures to adapt to the new table structure. This is inevitable, and modifying the table structure and filtering context definitely requires rewriting the measures.

(2)Do you mean when you drill through the one of the Source in Application/Contracts, you want to show the table filtered by it and display the other column to show the Contracts/Application's Source name according to the every ID?

If this , we can create two measures like this to put in the table visual:

Application Source = CALCULATE(MAX('Lead Application Contract Sources'[Source] ) , 'Lead Application Contract Sources'[ID] = MAX('Lead Application Contract Sources'[ID]) ,'Lead Application Contract Sources'[Table]="Applications" ,ALL() )

Contacts Source = CALCULATE(MAX('Lead Application Contract Sources'[Source] ) , 'Lead Application Contract Sources'[ID] = MAX('Lead Application Contract Sources'[ID]) ,ALL() )

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

@v-yueyunzh-msft I apologize for the late response, but yes that is exactly what I want it to do.  However, I can't get the Applications Source or Contracts Source columns to work.  Applications Source is blank for all of them, and Contracts Source is the same for all of them:

Application Source  = CALCULATE(MAX('Lead Application Contract Sources'[Source Category]) , 'Lead Application Contract Sources'[Candidate ID] = MAX('Lead Application Contract Sources'[Candidate ID]) ,'Lead Application Contract Sources'[Table]="Applications" ,ALL()      )

Contacts Source 2 = CALCULATE(MAX('Lead Application Contract Sources'[Source Category] ) , 'Lead Application Contract Sources'[Candidate ID] = MAX('Lead Application Contract Sources'[Candidate ID])  ,'Lead Application Contract Sources'[Table]="Contracts",ALL()      )

[v-yueyunzh-msft]

Hi ,  @aashton 

Thanks for your quick reponse! And i am sorry for that this can not help you solve the problem.

This may be a problem caused by different contexts, but I don't have your test data so it's hard to tell what the problem is. If it is convenient, can you provide me with some test data (which does not contain sensitive data and can reproduce your problem), and then provide me with the expected results you want in a table form, so that I can help you better?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

Hello @v-yueyunzh-msft !

So I did get the 2 measures to work, Applications Source and Contract Source. The drill-through is still kind of funny, sometimes it shows records from a different source than the one I clicked on...still trying to work that out.  But I have a second part of this I am trying to figure out.  See attached I've created two measures, for Application Count and Contract Count.  I need to display these in a matrix by source.  I've put these 2 new measures on Page 2 for the drill-throuh....However, when drill-through  on the matrix, it only brings the filter of the source not the tables.  So if I click on Agency Application Count of 5, it shows the 5 Application, plus any contracts with the source of Agency.  Is there a way to get it to filter on the table and source?

https://drive.google.com/file/d/1ZCASD5RnFeqP_lO-da_qdVS7q_zvi7tb/view?usp=sharing

[v-yueyunzh-msft]

Hi， @aashton 

According to your description, when you drill through the table , it will not filter the [Table] field?

This is due to we put the measure as the value and we do not put the dimension on the row or columns.

So , if you want to filter the [Table] field in your table visual, the best way is add the [Table] field on the columns.

And then we create a measure like this:

Count = CALCULATE(DISTINCTCOUNT('Lead Application Contract Sources'[ID]), 'Lead Application Contract Sources'[Table]=MAX('Lead Application Contract Sources'[Table]))

Then we can drill through like the bar chart .

If i misunderstand your need , you can share me the end result in a situation as a table so that we can make this question more easiler.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

@v-yueyunzh-msft  Thank you, yes that works.  But I have more measures i need to display in this matrix, so Table in the columns screws that up (the other measures aren't by table), for example:

[v-yueyunzh-msft]

Hi, @aashton 

Thanks for your quick response! And according to your description, you have other measures need to put in this Matrix . And for drill through , it need the dimension so that we can push the filter to other page .

For your need , i think we need to create a dimension table as the column headers.

Here are the steps you can refer to :

(1)We can use the "Enter Data" to create a dimension table like this:

And this [Column1] is used to sort the [Type] field.

(2)Then we need to create a measure to return the according value :

Measure = var _type = MAX('Table'[Type])
var _name = MAX('Table'[Name])

return
IF(_type = "Applications" && _name = "Count" , [Application Count] , 
IF(_type = "Contracts" && _name = "Count" , [Contract Count] ,
IF( _name = "Other Measure" ,[Other Measure] ,
IF(_name = "Other Measure2" ,FORMAT([Other Measure 2],"0.00%")))))

Then we can put the fields on the visual :

(3)Then we need to create another measure like this:

Measure 2 = IF(  MAX('Lead Application Contract Sources'[Table]) in VALUES('Table'[Type]) ,1,0)

We can put this in the destination page visual on the "Filter on this visual":

(4)Then we can drill through from this table :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

@v-yueyunzh-msft This is great, thank you.  How is source related to the new table?  I am getting an error, can't determine the relationship:

[v-yueyunzh-msft · Accepted solution]

Hi , @aashton 

According to your screenshot,I see you have created the dimension table.For this table is used for the columns headers . And you put the right place,the row header is used by the fact table.
For your need ,we do not need to create relationship between the dimension table and the fact table.
Instead we need use the measure to get the value without relationships between tables.You do not worry about you get an error code when you put this three fields on the visual,we just need to create a measure to put on the value field so the error code will disappear!
And the dimension table is created by which fields you need to put on the column headers,in my test data ,they are “application count,contracts count,other measure ,other measure 2”.You need to modify this dimension table in your side based on your situation.
And then you can see the [Measure],this is to get the value based on the column headers,you also need to modify this measure based on your situation!
The [Measure2] is used for the drill through due to now when we drill through we convert the [Type] field instead of the [Table],so we need to create a new measure to transfer this filter to what we want !

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

@v-yueyunzh-msft  Ohhhh OK, I understand.  I think I have it working...one more question...How did you get Other Measure and Measure 2 next to the Counts when drilling down the matrix?  When I drill-down, it only shows my extra measures and the total of count (not by table), and when I expand the entire hierarch, I get below:

Drilled-down once:

Expand hierarchy:

My Measure:

Measure Drill-Through = var _type = MAX('LAC Drill-Through'[Type])

var _name = MAX('LAC Drill-Through'[Name])

return

IF(_type = "Lead" && _name = "Count", [Total Lead LAC],

IF(_type = "Applications" && _name = "Count", [Total Application LAC],

IF(_type = "Contracts" && _name = "Count", [Total Contracts LAC],

IF(_name = "Lead to App Conversion", [Lead to App Conversion combo],

IF(_name = "App to Contract Conversion", [App to Contract Conversion],

IF(_name = "Lead to Contract Conversion", [Lead to App Conversion combo]))))))

[v-yueyunzh-msft]

Hi, @aashton 

Thanks for your quick response ! Accoridng to your description, when you drill  down the table visual , it dose not display the first hirearchity . 

For this , we need to click the "Expand all down one level in the hierarchy" so that we can keep the first hirearchity show in this visual:

And you can also see this dimension table we have created  and it corresponse each level of relationship.We can understand by this image :

And we can also see the drill down differnce in this document , you can kindly refer to:

Drill mode in the Power BI service - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[aashton]

@v-yueyunzh-msft  Yes, thank you, that works.  It looks like I was overthinking it.  But, how/could this work in a matrix?  I have a matrix like below with multiple measures along the top (Lead, Application and Contracts are the values for field table):