topic Re: Disconnected Table - Filter the List of Items on X Axis (and not measure), is it Possible? in DAX Commands and Tips

Disconnected Table - Filter the List of Items on X Axis (and not measure), is it Possible?

VbOnline — Wed, 28 Sep 2022 15:31:11 GMT

All

I have a situation where in, based on selection of a particular "Station" from a slicer, I need to get all "OTHER" stations which are of same Size (by number of trains arriving and departing) and show those other stations in the Bar Graph X Axis and a measure for those station.

I have seen lot of posting or video's where, people have used disconnected table to calculate a measure and restrict the measure. But this is restricting the Measure (typically on Y Axis) but no example to filter X axis.

The below is my base data set:

StationCode	StationSize	StationArrivals	StationDepartures
HYD	5	22	31
SEC	5	21	33
MAS	8	34	45
BOM	12	54	64
BLR	5	22	43
BPL	4	34	22
KOL	12	34	23

The user will come and select a station, lets assume HYD. Based on that selection, I can get the Size by using SELECTEDVALUE or LOOKUP. So, I get a value of 5.

Using that 5, I need to pick up all stations with size of 5, that will give me BLR, SEC and HYD.

For these three stations, I need to show a graph where the Station is on X Axis and the arrival/departure count is on Y Axis.

How is it possible to restrict the X axis (stations to show), is Disconnected table a solution? For example, the below is how the bar graph will look like but it will only show 3 stations than 7 stations in my table.

Any help is appreciated, either via Disconnected Table or any other mechanism.

Re: Disconnected Table - Filter the List of Items on X Axis (and not measure), is it Possible?

johnt75 — Thu, 29 Sep 2022 09:29:16 GMT

You could set up a disconnected table as

Station slicer = ALLNOBLANKROW(Stations[Name], Stations[Size])

and use that in a slicer. Then create a measure like

Station is visible = VAR VisibleStations = CALCULATETABLE ( VALUES ( Stations[Name] ), TREATAS ( VALUES ( 'Station slicer'[Size] ), Stations[Size] ) ) RETURN IF ( SELECTEDVALUE ( Stations[Name] ) IN VisibleStations, 1 )

and use that as a visual level filter on your bar chart, to only show when the value is 1.

Re: Disconnected Table - Filter the List of Items on X Axis (and not measure), is it Possible?

v-xiaosun-msft — Thu, 29 Sep 2022 09:30:31 GMT

Hi VbOnline ,

According to your description, here is my solution.

Create a measure to get the size when you select the “StationCode” in slicer.

Measure = IF ( SELECTEDVALUE ( 'Table'[StationCode] ) = MAX ( 'Table'[StationCode] ), MAX ( 'Table'[StationSize] ) )

Create a table.

Table 2 = SUMMARIZE ( 'Table', 'Table'[StationCode], 'Table'[StationSize] )

Create a measure to get the “StationCode” with the same size.

Measure2 = IF ( 'Table'[Measure] = MAX ( 'Table 2'[StationSize] ), 1 )

Put columns to a chart.

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

Re: Disconnected Table - Filter the List of Items on X Axis (and not measure), is it Possible?

VbOnline — Thu, 29 Sep 2022 12:23:55 GMT

v-xiaosun-msft Thanks for your inputs and the PBIX file, I definately made progress with regards to restricting the stations in the bar graph meeting measure2. Only Problem next is, the Measures (Arrivals and Departures), insetad of showing the Bar Graph departures/Arrivals for specific stations, it is showing TOTAL of all Arrivals or Departures for the stations in the Graph.

I tried to create a measure (for example, Arrivals Divided by Departures to get a Percent) and add to the bar graph Y axis but that added all the stations.

Any thoughts on how to get the measure accurate as appropriate for each station?

Thank You.

Re: Disconnected Table - Filter the List of Items on X Axis (and not measure), is it Possible?

VbOnline — Thu, 29 Sep 2022 13:18:32 GMT

johnt75 I think this did the trick, I am able to use the methodology you explained to get the output I want. I use case I have given is for one criteria which is "Size", there are couple of more criterias I need to add, let me extend your methodology with those and get back. thanks a lot for your inputs.