power bi desktop

[Anonymous]

Hello All,

I have a dimension table that has client name and intouch id column. Also I have other fact table that contains clients other information and am filtering them through Report Level Filter by client name, these both are shown in report using table visual. They both have relationship by intouch id column. Now in dimension table there are many clients that have blank intouch id.

So now if i select a client name that has blank intouch id in my fact table by report filter , then all records that have same blank intouch id will be displayed in my dimension table but i want the client detail in dimension table to be displayed of only that particular client not others whose intouch is also  blank.

How can i achieve this by using filter pane or other method.

Sorry I cant share the data.

Thanks in advance,

Chahat

[v-lili6-msft]

hi, @Anonymous 

For blank value in visual, it is due to two mismatched relational columns.

For example:

in dimension table 

intouch id

1

2

3

4

But in fact table 

intouch id

2

3

4

5

When the two tables are created the relationship by the intouch id column, it will lead to the problem.

And you could try this way:

Darg intouch id Page/Report level filter and then set filter out blank

Best Regards,

Lin

[Anonymous]

@v-lili6-msft  thanks for that but this is not what I am asking for, I want that when I filter a particular client name Eg: american national bank  that has intouch id blank , I want my dimension table to display american national bank only ,not other banks that also have blank intouch id.

My dimension table is displaying all banks that have intouch id  blank,this is not i want. I want only american national bank to be displayed only. Both Tables are connected by itouch id so dimension table displays all banks that have matching itouch id to the one selected in fact table through report level filter.

I hope this makes my question clear.

Regards

Chahat

[v-lili6-msft]

hi, @Anonymous 

I'm a little confused about your description.

Sample data and expected output would help tremendously.

Could you please share a simple pbix file created by virtual data and expected output.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

@v-lili6-msft  yea. Both tables table 1 (fact table) and table 2 (dimension table) are connected by id. I am filtering my data by id (of fact table) in report level filter. What I want is in case of blank id, i want only selected client name to be displayed not all other banks(like Wells Fargo) in table 2 that also has blank id to be displayed.

So i dont want Wells fargo in table 2 .

[v-lili6-msft]

hi, @Anonymous 

You could use LOOKUPVALUE Function to add a tag column in table2, eg.

tag = IF( AND(ISBLANK(LOOKUPVALUE(Table1[Client name],Table1[Client name],Table2[Client name]))=TRUE(), ISBLANK(Table2[Id])=TRUE()),1,2)

then drag it into report level filter and set is "2"

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Best Regards,

Lin

[Anonymous]

@v-lili6-msft  Thanks for your solution but it doesnt seems to be working. It is assigning 2 to every row in the table bcoz my table1 has all the bank names (duplicates as well since its a transactional data) not only american bank (which is filtered by report level filter).and my table 2 has  all distinct bank names . so lookup statement is not working.

regards 

Chahat

[v-lili6-msft]

hi, @Anonymous 

If so, i think you need to add a measure instead of a column to filter the data.

Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

So, could you please provide more sample data and expected output based on it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

I created a measure where

Measure = If ( HasOneValue('table1'[Client name]) , Values ('table1'[Client name]) 

This seems to be working but the drawback is I cant select multiple client names my report level filter only one.

Then I created a calculated column where

tag = IF(AND(ISBLANK(IF(table2[Client name] = [Measure],[Measure],BLANK()))=TRUE(),ISBLANK(table2[Id])=TRUE()),1,2) but the inner IF Statement doesnt seems to be working.

I dont understand why.

regards

Chahat

[v-lili6-msft]

hi, @Anonymous 

The logic of a measure and column is not the same, so don't put them into together.

For your case, It's better to use virtual data to create a simple sample pbix file and share with us to test, that will be a great help for the case.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft 

I can tell you that all the table2 is extracted from table 1 that has client details, Id and other columns. and each client name has no of records. So there is repetition.  But table2 has only the distinct client names and their id . Both are connected by id and in report level filter i am using Client name of table1.

regards 

Chahat