...and another inexplicable feature with slicers

[martingaleh]

I have two columns in my table based on one slicer.
=values(QFocus[FQ])
and
=values(QFocus[FY])

They're both based on the same slicer. the FY one works. You can see, because it's the one next to the one with the error: FQ. Why does FQ produce an error?

Here's my slicer thingy, which I still don't know how to name

 

[akice]

You seem to be mixing up terms. A slicer is a UI filtering tool that is based on the unique values of a column. It is a run time visualization. You can't use it to impact column values during data load.

And calculated columns can only return a scalar value. Since the only unique value in the 'FY' column is '2018', the column is created. 'FQ' has 4 possible unique values which makes the VALUES() call ambiguous. DAX doesn't know which value you want. The VALUES() function returns a single column table of all the unique values visible in the current filter context. Since you have an empty filter context in your calculated column, it tries to retrieve all 4 values which is not allowed.

 

[martingaleh]

http://www.daxpatterns.com/parameter-table/

This whole post is about getting the slicer selection into the model. They're using the values function and it's returning all the values that have been selected in the slicer. As there is only one value selected, I figured that is the one they're using. I looked at their demo workbooks, the data is there.

 

[akice]

I am very much aware of daxpatterns and parameter tables and sorry but that is not what the article is saying. The parameter table is a disconnected table (not related to any other table) that segments values in ranges. No value in your fact table can be in two ranges at the same time. You then write a calculated column to return a single value from the parameter table that meets a row criteria in your fact table. The VALUES() function will convert a single column, single row value to a scalar value which is why it is being used. But it is up to the programmer to make sure VALUES() evaluates to only a single value. Once completed then, yes, you can create a slicer off the newly created calculated column as a filter to the model. So again, slicers are a run time visualization & filter and are not used at data load. You seem to be mixing slicers with parameter tables.

This is the daxpattern you should read to better understand:

http://www.daxpatterns.com/static-segmentation/

 

[martingaleh]

Yes, I finally figured it out. I figured out what was happening. When you make the pivot table from the editing thing, all the tables in the data model are available to the pivot table whether its' connected or not, including the fake table we made for the slicer. The slicer just slices the fake table when the values() is being evaluated. But here is the critical thing. Yes, so critical. In order for the slicer to slice, it has to be CONNECTED to the pivot table. This is in no documentation about parameter tables. Maybe there's a special section on slicers, but this mechanism is not designed for fast learning. Then when the values is evaluating (with Italian accent) the filter context trickles through to the dax. The slicer table evaluates to one value. The value is available for use.

In the mean time, I've been trying to get the value from the slicer and not the underlying table because in every other programming language I know involving a ui or events, that's how you get the data. They really do not want you to learn this language quickly by making it so unique and convoluted. Then they trick you with the slicer name in the slicer settings so you'd think it's like every other language. In fairness, Power Query is also pretty unique, but it is by far the fastest and easiest language to learn and use. I picked it up in 3 hours and can do things I can't easily do in nearly every other etl package. If there was only a COM handle on the data table refresh, I'd use that thing on the front end and trigger the refresh from the command line before picking up the processed table with talend or some such instead of refactoring it, which is what I have to do now.

There is no way the power query and the Power Pivot people are in the same building. I can't accept it. Just complete opposite implementation quality.

 

[akice]

all the tables in the data model are available to the pivot table whether its' connected or not

Yes.

In order for the slicer to slice, it has to be CONNECTED to the pivot table.

Not necessarily. Usually it is, but assuming parametertable is not related to any others, you can do this:

measure :=
VAR slicer_selection =
    IF (
        HASONEVALUE ( parametertable[CustomerName] ),
        VALUES ( parametertable[CustomerName] ),
        "Microsoft"
    )
RETURN
    CALCULATE (
        SUM ( Factable[Sales] ),
        dimensionCustomer[CustomerName] = slicer_selection
    )

the filter context trickles through to the dax.

Yes, there is automatic table filter propagation from the one side of the relationship to the many.

The slicer table evaluates to one value.

Again, not necessarily. A table with multiple rows of values can also, and often is, be used as filters to calculate a value.

They really do not want you to learn this language quickly by making it so unique and convoluted.

It is unique - but I wouldn't give it up now. Way, way better than old way of doing things for my use. I was like you four years ago. Trying to apply what i know about other languages to Dax. It actually was a handicap in many ways as opposed to someone who only knew Excel.

I picked it up in 3 hours and can do things I can't easily do in nearly every other etl package. If there was only a COM handle on the data table refresh

Power Query ? or the M language? And in Excel 2013 & 2016 there is an object model so you can use vba to trigger a data refresh - but i personally have never used it and to be honest have no idea how extensible it is.