Filter based on dimension to filter fact table

[Anonymous]

Hi All,

I have a requirement to create a set of filters based on one dimension table named Groups to filter the fact table. My table structure is below

Company Organization Support Group UserKey
ABC DEF GHJ 23
ABC DEF NEK 35
ABC WER LKL 56
ABC WER RTY 2
RTY IOP FGH 32
RTY IOP DSE 89
RTY QWE BNM 1
RTY QWE XCV 34

This Group table is referencing the fact table basis the user id. Now I have other dimensions that are linked to the fact table on other keys. My requirement is to create filters on Company, Organization and Support Group such that the fact table is filtered.

There are some measurements that I have created in the fact table :

IncidentCount = CALCULATE(count(Incident[number]),filter(RELATEDTABLE('Fact'),'Fact'[IncidentKey])). This is working fine but when I am adding the following filter

ChangeCount = CALCULATE(COUNT(Change[number]),filter(RELATEDTABLE('Fact'),'Fact'[ChangeKey]),filter(RELATEDTABLE('Date'),'Date'[DateKey]),

filter(RELATEDTABLE(Groups),Groups[UserKey]

))

I am getting blank values when I select Company column value in my report as which is added as a filter.

I want to have three filters on first, second and third columns. Selecting any of them should filter my measure.

Please help

[Anonymous · Accepted solution]

As per your commens my understanding is you want to filter measure based on dimension filters.

First of all create relationship between your fact table and dimension table (Many to one).

Now add required dimension column in slicer and create measures.

Let's say you have added group column in slicers.

and you have amount in fact table.

Create new measure=sum(fact[amount])

Drag this measure and group into table visual.

And this visual will display the result as per slicer selection.

In your case i am not sure what is incident number?

I assume incident and change are your table name of dimension.

Create measure for the key of incident number and change number that is

Count Incident =count(Fact[Incident key])

You need to add something from fact table to csee the result in table visual.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.