Need help with few DAX expressions

[eniX]

Hi guys,

I have a use case, where I have to analyse automotive parts. I am looking for such parts, which I'm not selling currently, but which I should sell, because those parts have high "sell-potential":

• Potential to be sold depends on many criteria i.e. car registration figures (and the care age), search hits in online stores, sales figures in the past and so on.
• Additionally, a part can potentially have alternative parts, which can be used in a particular car instead of it.
• Finally, I have information about the stocks, where the parts are stored. A part can, but doesn't have to, have a "stock-state". Y = on stock, N = not on stock.

Data Sources I have:

• Assignment parts-to-cars
• Items stock information (on region level)
• Some metrics about cars (on region level)
• Regions look-up table

In the report I need some filters and two tables - parts and alternative parts. slicer-filters should filter parts-table only. when I select any part in the parts-table the alternative parts-table should be filtered according to the selection.

Starting point:

• parts_cars is the table where parts are assigned to cars
• I copied this table, because I need it twice on the canvas 
• Because M2M- relationships are not possible I created a bridge table (carsBridge)
• carsMetrics is the table where you can find several metrics for particular car AND region
• partsStock is the table where you can find the storage-state for particular part AND region
• regions is the look-up table for regions. In every region there is exactly one stock. That's why we can treat region as stock and vice versa ( region = stock)

Problems/To-Dos/Aims:

• In the alternative parts table I would like to see two additional columns "state" and "state_0"
  • "state" should show the state for the currently selected region. if no region selected (slicer-value: ALL) it should be blank. Due to it's dynamic nature, I think it has to be a measure.
  • "state_0": stock with id 0 is the main stock of the company. That's why this state should always be shown (in a separate column). I think here we need a calculated column. I tried following DAX:

  • state_0 = LOOKUPVALUE(partsStock[state] ; partsStock[stock] ; "0" ; partsStock[part] ; partsStock[part] )

• I also need two additional slicers, which should filter the parts table:
  • I would create two calculated columns and use them as slicer
  • first calculated column: "hasNoAltParts". Here I need a DAX with follow semantics:
    • Check if current part (row context) has alternative parts. If it has at least one, then "Y", else "N"
    • I tried following DAX and I think it gives me results I need:

      IF ( COUNTROWS (RELATEDTABLE('cars_parts')) - 1 ; "Y" ; "N" )

  • second calculated column: "hasNoAltPartsOnStock". This is the most complicated one. Semantics:
    • Check if current part has alternative parts. If it has alternative parts, iterate over them and check, whether there is at least one alternative part with stock state "Y". If so, return "N", else "Y"
    • Do I need additional sub-expressions here or is it possible to write a single formula?

-----------------------------

Are my DAX expressions right? Can you help me with the missing ones?

I would like to add the ,pbix-file, but unfortunately I can't any option to do so...

@Zubair_Muhammad - can you help me one more time my friend?^^

[eniX]

---

@eniX wrote:

• regions is the look-up table for regions. In every region there is exactly one stock. That's why we can treat region as stock and vice versa ( region = stock)

---

exactly 🙂

[v-ljerr-msft]

Hi @eniX,

Could you share a sample pbix file with some sample/mock data, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Zubair_Muhammad]

@eniX

Create a dummy data model in Excel similar to your situation and share the file via ONEdrive or googledrive

Hopefully, Someone will help you out

[eniX]

thank you, any help is appreciated:

https://drive.google.com/open?id=1KN_V6zebTpuuCIMo3U_hpQHtIPGC3qzH

[eniX]

anyone? 😞

[Anonymous]

Is the Stock field in PartsStock table holds region number? or What does that Stock value refer to